pspg - two new features

I wrote two new features to pspg.

First feature is a option --skip-columns-like. Now, it can be used only for browsing csv or tsv documents. When this option is used, then specified columns (specified by substring of name) are not displayed. It can be useful for psql PostGIS data. More time some GEO data has not sense to display. If you have wide table with lot of columns, then can be nasty to write list of columns every time. Example

export PSPG="--skip-columns-like='geom$'

Then if you use psql, then you can write query like SELECT * FROM roads, and all columns like roads_geom will be removed from presented table. You have to switch output format to csv by \pset format csv (it is available from Postgres 12).

Second new feature is good for wide columns too. If you column is wide, and column name not, then you can see only white space and you have to scroll to left or to right to see column name. With last commit, the column name is almost all time visible - when it is possible.

dbms_sql

Like lot of other people I had different plans to spring this year. Instead a walk to Santiago, I sit at home. On second hand it is time to finish some repairing on my house and write some code.

I finished prototype of dbms_sql extension. This extension implements part of interface of dbms_sql package. A design of this package is related to requirements and possibilities at 30 years ago. Today is not necessity to use similar functionality on Postgres, and probably on Oracle too. But a goal of this extension is similar to goal of Orafce extension - reduce a work that is necessary for porting some applications from Oracle to Postgres. Only part of interface is implemented, but some interesting parts like bulk DML operations are implemented.

On second hand, this extension can be good example of implementation's patterns of PostgreSQL extensions. Because implemented interface is relative rich, then it uses lot of internal PostgreSQL's API.

This extension will be included to Orafce in future. Now it is separated extensions from two reasons:

the code is not mature (I just wrote prototype), but it should be good enough for testing and usage (the code is not too complex).

it requires PostgreSQL 11 and higher. Orafce requires 9.4 and higher. So I'll wait to end of support of PostgreSQL 10.

Examples:

do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
  a := ARRAY[1, 2, 3, 4, 5];
  b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
  ca := ARRAY[3.14, 2.22, 3.8, 4];

  call dbms_sql.bind_array(c, 'a', a, 2, 3);
  call dbms_sql.bind_array(c, 'b', b, 3, 4);
  call dbms_sql.bind_array(c, 'c', ca);
  raise notice 'inserted rows %d', dbms_sql.execute(c);
  call dbms_sql.close(c);
end;
$$;


do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)');
  call dbms_sql.define_array(c, 1, a, 10, 1);
  call dbms_sql.define_array(c, 2, b, 10, 1);
  call dbms_sql.define_array(c, 3, ca, 10, 1);

  perform dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c) > 0
  loop
    call dbms_sql.column_value(c, 1, a);
    call dbms_sql.column_value(c, 2, b);
    call dbms_sql.column_value(c, 3, ca);
    raise notice 'a = %', a;
    raise notice 'b = %', b;
    raise notice 'c = %', ca;
  end loop;
  call dbms_sql.close_cursor(c);
end;
$$;