Thread: [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
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
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