Re: PERSISTANT PREPARE (another point of view)

Hello

2008/7/28 Milan Oparnica <milan(dot)opa(at)gmail(dot)com>:
> Pavel Stehule wrote:
>>
>> Hello
>>
>> this is near my implemenation of stored procedures - it's not far from
>> your view on prepared statements. There result of any unbound select
>> is directly forwarded to client - there is little bit different
>> syntax, but idea is same
>>
>>
>> create procedure foo(params)
>> as $$
>> begin
>> select a, b, c from tab1 -- unbound select
>> select ....
>> end;
>>
>> and you can call with CALL statement. Curent implementation of SRF
>> function in plpgsql sn't eficient for big results - it needs
>> materialised table in memory. Pipeline Oracle's functions are better,
>> but we are limited one thread architecture.
>>
>> regards
>> Pavel Stehule
>>
>
> Yeah, this would be pretty the same.
>
> I just didn't understand if this is already implemented in PG ?
>

no - I have workin prototype now - and I am searching sponsors for
finishing this work.

http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
http://okbob.blogspot.com/2007/11/first-real-procedures-on-postgresql.html

But it's really similar to what do you want and it's can be
interesting together with plpgpsm for you, because plpgpsm (SQL/PSM)
allows one statement procedures, like:

create procedure foo(...) as $$
select * from tab;
$$ language plpgpsm;

so this is standard syntax for persistent prepared statement :)

regards
Pavel Stehule

> I've tried to create a PROCEDURE in QueryTool of pgAdminIII and I get
> following result:
>
> ERROR: syntax error at or near "PROCEDURE"
> LINE 1: CREATE PROCEDURE aatestubnd(refcursor, insklid integer, inda...
> ^
>
> ********** Error **********
>
> ERROR: syntax error at or near "PROCEDURE"
> SQL state: 42601
> Character: 8
>
>
> If I try with CREATE FUNCTION I get this result (as expected):
>
> ERROR: function result type must be specified
>
> ********** Error **********
>
> ERROR: function result type must be specified
> SQL state: 42P13
>
>
> Is this because of QueryTool's limitations or is this feature not yet
> implemented in Postgre ?
>
> Though, CREATE PROCEDURE should be a valid SQL 92 standard procedure.
>
> Best regards,
>
> Milan Oparnica
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>