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.

Labels: ,

2 Comments:

At November 8, 2007 at 12:38 AM , Blogger Ivan said...

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

 
At November 8, 2007 at 5:04 AM , Blogger Pavel Stěhule said...

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.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home