Pages

Wednesday, November 7, 2007

First real procedures on PostgreSQL

I am working on an enhancing of plpgsql. I would to add to plpgsql procedures and methods. Methods are flagged functions with different calling convention. Procedures are total different beast. Now I am able to run procedures without parameters. I would to support parameter passing by reference. It allows to take reference for any variable and potentially some interesting features (like string builder or array builder etc). What is best, I need not modify system tables. Procedure is function (from pg_proc perspective) that returns PROCEDURE type (it will be used for exit status).

postgres=#
create or replace procedure print()
as $$
begin
raise notice 'ahoj';
end;
$$ language plpgsql;
CREATE PROCEDURE

postgres=#
create or replace function test()
returns void as $$
begin
call print();
return;
end;
$$ language plpgsql;
CREATE FUNCTION

postgres=# select test();
NOTICE: Found ()
CONTEXT: PL/pgSQL function "test" line 4456553 at CALL
NOTICE: OID: 16821
CONTEXT: PL/pgSQL function "test" line 4456553 at CALL
NOTICE: ahoj
CONTEXT: PL/pgSQL function "test" line 4456553 at CALL
test
------

(1 row)

postgres=# \df print
List of functions
Schema | Name | Result data type | Argument data types
--------+-------+------------------+---------------------
public | print | procedure |
(1 row)

There is lot of work still, but first step is done.

2 comments:

  1. Is this different then the PL/pgPSM and do you expect these changes to make it to 8.4?

    ReplyDelete
  2. I hope, so this feature will be part of 8.4. There will be only small or none differences between plpgsql and plpgpsm (about procedure's possibilities). Current PL/pgPSM doesn't support procedures too.

    ReplyDelete