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;
$$;