[BASIC FEATURES] stored procedures in Postgresql ?
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.
"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
Import Notes
Reply to msg id not found: JrmeCouratsmessageofThu6Dec2001144518+0100
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
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
Import Notes
Reply to msg id not found: HolgerKrugsmessageofFri7Dec2001083047+0100
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
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
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
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
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
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
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
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
Attachments:
dblink_README.difftext/plain; name=dblink_README.diffDownload
*** README.dblink.orig Mon Jun 18 12:09:50 2001
--- README.dblink Wed Dec 12 19:57:34 2001
***************
*** 82,88 ****
Example usage
! select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd','select f1, f2 from mytable');
==================================================================
--- 82,89 ----
Example usage
! select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
! ,'select f1, f2 from mytable');
==================================================================
***************
*** 118,161 ****
==================================================================
! NOTE: If you need to provide selection criteria in a WHERE clause, it is necessary
! to 'fake' a UNION, e.g.
! select
! dblink_tok(t1.dblink_p,0) as f1
! ,dblink_tok(t1.dblink_p,1) as f2
! from
! (
! select dblink(
! 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
! ,'select f1, f2 from mytable'
! ) as dblink_p
! union all
! select null where false
! ) as t1
! where
! dblink_tok(t1.dblink_p,0) = 'mycriteria';
!
! in order to work around an issue with the query optimizer. A more convenient way to approach
! this problem is to create a view:
!
! create view myremotetable as
! select
! dblink_tok(t1.dblink_p,0) as f1
! ,dblink_tok(t1.dblink_p,1) as f2
! from
! (
! select dblink(
! 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
! ,'select f1, f2 from mytable'
! ) as dblink_p
! union all
! select null where false
! ) as t1;
Then you can simply write:
! select f1,f2 from myremotetable where f1 = 'mycriteria';
==================================================================
--- 119,134 ----
==================================================================
! A more convenient way to use dblink may be to create a view:
! create view myremotetable as
! select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
! from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres password=postgres'
! ,'select proname, prosrc from pg_proc') as dblink_p) as t1;
Then you can simply write:
! select f1, f2 from myremotetable where f1 like 'bytea%';
==================================================================
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