Hello,

We have to evaluate the possibility to integrate an open source RDBMS in our
software developments.
I checked some stuff on Postgres, but I now have to find out whether
Postgres integrates Stored Procedures as a feature.

Can anyone tell me if it does ? In that case, my company would use this
rdbms for several future products.

Many thanks in advance.

Additionally, if anyone could give me some pointers to it in the huge
postgres doc...

Regards,

J�r�me Courat
Java developer.



Thu, 6 Dec 2001 14:45:18 +0100
"J���r���me Courat" <jerome.courat@gecko.fr.eu.org>
[BASIC FEATURES]  stored procedures in Postgresql ?
"Jï¿½rï¿½me Courat" <jerome.courat@gecko.fr.eu.org> writes:

> Hello,
> 
> We have to evaluate the possibility to integrate an open source RDBMS in our
> software developments.
> I checked some stuff on Postgres, but I now have to find out whether
> Postgres integrates Stored Procedures as a feature.

Depends on your definition of "stored procedure".  Postgres allows
user-written functions, stored in the database, and callable from
queries.  What these functions can't currenty do is return result
sets, which is what a lot of people mean by "stored procedure".

However, it's my understanding that in 7.2 (which is currently in
beta) functions can return open cursors, which gives you a lot of the
same functionality as returning result sets.

Also, functions can be written in several languages, including Perl,
Python, and Tcl as well as straight C and PGSQL (which is similar to
Oracle's PL/SQL).

> Can anyone tell me if it does ? In that case, my company would use this
> rdbms for several future products.

I hope my response has been helpful.

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

06 Dec 2001 13:13:18 -0500
Doug McNaught <doug@wireboard.com>
Re: [BASIC FEATURES]  stored procedures in Postgresql ?
On Thu, Dec 06, 2001 at 01:13:18PM -0500, Doug McNaught wrote:
> However, it's my understanding that in 7.2 (which is currently in
> beta) functions can return open cursors, which gives you a lot of the
> same functionality as returning result sets.

Gives it also the possibility to returning result sets to the client ??

I want to code a scenario (e.g. within a rule) like:

  id = nextval('idseq');
  INSERT INTO tab ( id, ... ) VALUES ( id, ... );
  /* return the result of the following query to the user: */
  SELECT * FROM tab WHERE tab.id = id;

The problem is that there is no way to put the value of the `id'
variable into the last query, when the last query is put into a place,
where its result set is returned to the client (e.g. as the last query
in a rule).

Can I return an open cursor to the client ? Can I otherwise return the
result set of an open cursor, which was returned by a server-side
function, to the client ?

-- 
Holger Krug
hkrug@rationalizer.com

Fri, 7 Dec 2001 08:30:47 +0100
Holger Krug <hkrug@rationalizer.com>
Re: Using Cursor in PostgreSQL 7.2
Holger Krug <hkrug@rationalizer.com> writes:

> On Thu, Dec 06, 2001 at 01:13:18PM -0500, Doug McNaught wrote:
> > However, it's my understanding that in 7.2 (which is currently in
> > beta) functions can return open cursors, which gives you a lot of the
> > same functionality as returning result sets.
> 
> Gives it also the possibility to returning result sets to the client ??

See the docs;  I don't know much more about it than what I
posted--haven't played with it myself yet.

-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

07 Dec 2001 12:07:40 -0500
Doug McNaught <doug@wireboard.com>
Re: Using Cursor in PostgreSQL 7.2
C functions returning sets are entirely possible, and there's even some
documentation about how to do it in src/backend/utils/fmgr/README (which
needs to be transposed to present tense and moved into the SGML docs,
but it's better than nothing).

There is at least one simple example in the 7.2 sources: see 
pg_stat_get_backend_idset() in src/backend/utils/adt/pgstatfuncs.c,
and observe its usage in the pg_stat views, eg at the bottom of
http://developer.postgresql.org/docs/postgres/monitoring-stats.html

There is not presently any support for this sort of thing in plpgsql
or any of the other PL languages, however.

			regards, tom lane

Fri, 07 Dec 2001 12:38:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Using Cursor in PostgreSQL 7.2 
Tom Lane wrote:

> C functions returning sets are entirely possible, and there's even some
> documentation about how to do it in src/backend/utils/fmgr/README (which
> needs to be transposed to present tense and moved into the SGML docs,
> but it's better than nothing).
> 
> There is at least one simple example in the 7.2 sources: see 
> pg_stat_get_backend_idset() in src/backend/utils/adt/pgstatfuncs.c,
> and observe its usage in the pg_stat views, eg at the bottom of
> http://developer.postgresql.org/docs/postgres/monitoring-stats.html
> 


It looks like the stats monitoring functions suffer from the same 
limitation that I hit with dblink:


lt_lcat=# SELECT pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT 
pg_stat_get_backend_idset() AS backendid) AS S; 
                        procpid | current_query
---------+---------------
    12713 |
    12762 |
(2 rows)


lt_lcat=# SELECT pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT 
pg_stat_get_backend_idset() AS backendid) AS S where 
pg_stat_get_backend_pid(S.backendid) = 12713;
ERROR:  Set-valued function called in context that cannot accept a set


lt_lcat=# SELECT pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT 
pg_stat_get_backend_idset() AS backendid UNION ALL SELECT 1 WHERE FALSE) 
AS S where pg_stat_get_backend_pid(S.backendid) = 12713;
  procpid | current_query
---------+---------------
    12713 |
(1 row)

The UNION is ugly but allows it to work. Tom discussed the reason this 
is needed on: http://fts.postgresql.org/db/mw/msg.html?mid=120239.

Joe




Fri, 07 Dec 2001 10:45:23 -0800
Joe Conway <joseph.conway@home.com>
Re: Using Cursor in PostgreSQL 7.2
On Fri, Dec 07, 2001 at 12:38:03PM -0500, Tom Lane wrote:
> C functions returning sets are entirely possible, and there's even some
> documentation about how to do it in src/backend/utils/fmgr/README (which
> needs to be transposed to present tense and moved into the SGML docs,
> but it's better than nothing).

Thank you ! I very appreciate your answer.

So what I have to do to let a PL/PGSQL function to return a set to the
client is:

1) let the PL/PGSQL return a cursor
2) write a general C wrapper function cursor_to_set(cursor) which gets a
   cursor and returns the result set

My additional questions:

* Is 2) possible if the nature of the cursor is not known in advance ?
* Is the implementation of cursor_to_set very complicated or can it done
  with the documentation cited in your mail ?

I think such a function cursor_to_set, if possible, would be very
useful, wouldn't it ?

> There is not presently any support for this sort of thing in plpgsql
> or any of the other PL languages, however.

Having `cursor_to_set' it would be half as bad !

-- 
Holger Krug
hkrug@rationalizer.com

Fri, 7 Dec 2001 20:06:11 +0100
Holger Krug <hkrug@rationalizer.com>
Re: Using Cursor in PostgreSQL 7.2
Joe Conway <joseph.conway@home.com> writes:
> It looks like the stats monitoring functions suffer from the same 
> limitation that I hit with dblink:

Urgh, you're right:

regression=# select * from pg_stat_activity;
  datid  |  datname   | procpid | usesysid | usename  | current_query 
---------+------------+---------+----------+----------+---------------
 3833396 | regression |    2625 |        1 | postgres | 
(1 row)

regression=# select * from pg_stat_activity where procpid = 2625;
ERROR:  Set-valued function called in context that cannot accept a set
regression=# 

This probably qualifies as a "must fix" problem.  I guess I'll have to
add the test for set-valued functions that I was reluctant to add
before.

			regards, tom lane

Fri, 07 Dec 2001 16:38:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Using Cursor in PostgreSQL 7.2 
Joe Conway <joseph.conway@home.com> writes:
> It looks like the stats monitoring functions suffer from the same 
> limitation that I hit with dblink:

I've added the missing checks in the planner; possibly you could get rid
of that UNION hack now.

			regards, tom lane

Mon, 10 Dec 2001 18:53:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Using Cursor in PostgreSQL 7.2 
Tom Lane wrote:

> I've added the missing checks in the planner; possibly you could get rid
> of that UNION hack now.
> 


*Moved to hackers*

I confirmed the UNION hack is no longer required. Thanks! Is it too late 
to change the README in contrib/dblink?


A side issue I noticed is that recent changes to contrib/*/Makefile seem 
to cause 'MODULE_PATHNAME' in *.sql.in files to become 
'$libdir/modulename' in the resulting *.sql files. Example:

in rtree_gist.sql.in:
-- define the GiST support methods
create function gbox_consistent(opaque,box,int4) returns bool as 
'MODULE_PATHNAME' language 'C';

becomes in rtree_gist.sql:
-- define the GiST support methods
create function gbox_consistent(opaque,box,int4) returns bool as 
'$libdir/rtree_gist' language 'C';

Same thing happens in (at least) dblink.sql, fuzzystrmatch.sql, and 
array_iterator.sql.

I'm not sure right off how to fix it though :(

Joe


Wed, 12 Dec 2001 09:42:45 -0800
Joe Conway <joseph.conway@home.com>
Re: [GENERAL] Using Cursor in PostgreSQL 7.2
Joe Conway <joseph.conway@home.com> writes:
> I confirmed the UNION hack is no longer required. Thanks! Is it too late 
> to change the README in contrib/dblink?

No, I don't think that's a problem.  Send a patch.

> A side issue I noticed is that recent changes to contrib/*/Makefile seem 
> to cause 'MODULE_PATHNAME' in *.sql.in files to become 
> '$libdir/modulename' in the resulting *.sql files. Example:

This is correct behavior now; in fact, full paths in CREATE FUNCTION
should be deprecated...

			regards, tom lane

Wed, 12 Dec 2001 12:53:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Using Cursor in PostgreSQL 7.2 
Tom Lane wrote:

> Joe Conway <joseph.conway@home.com> writes:
> 
>>I confirmed the UNION hack is no longer required. Thanks! Is it too late 
>>to change the README in contrib/dblink?
>>
> 
> No, I don't think that's a problem.  Send a patch.
> 

Here's a (documentation only) patch for the contrib/dblink README.

Thanks,

Joe
Wed, 12 Dec 2001 20:08:22 -0800
Joe Conway <joseph.conway@home.com>
Re: [HACKERS] [GENERAL] Using Cursor in PostgreSQL 7.2

Patch applied.  Thanks.

---------------------------------------------------------------------------


> Tom Lane wrote:
> 
> > Joe Conway <joseph.conway@home.com> writes:
> > 
> >>I confirmed the UNION hack is no longer required. Thanks! Is it too late 
> >>to change the README in contrib/dblink?
> >>
> > 
> > No, I don't think that's a problem.  Send a patch.
> > 
> 
> Here's a (documentation only) patch for the contrib/dblink README.
> 
> Thanks,
> 
> Joe

> *** README.dblink.orig	Mon Jun 18 12:09:50 2001
> --- README.dblink	Wed Dec 12 19:57:34 2001
> ***************
> *** 82,88 ****

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 13 Dec 2001 05:49:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] [GENERAL] Using Cursor in PostgreSQL 7.2