Thread
Commits
-
Support for OUT parameters in procedures
- 2453ea142233 14.0 landed
-
Support for OUT parameters in procedures
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2020-08-27T08:34:09Z
Procedures currently don't allow OUT parameters. The reason for this is that at the time procedures were added (PG11), some of the details of how this should work were unclear and the issue was postponed. I am now intending to resolve this. AFAICT, OUT parameters in _functions_ are not allowed per the SQL standard, so whatever PostgreSQL is doing there at the moment is mostly our own invention. By contrast, I am here intending to make OUT parameters in procedures work per SQL standard and be compatible with the likes of PL/SQL. The main difference is that for procedures, OUT parameters are part of the signature and need to be specified as part of the call. This makes sense for nested calls in PL/pgSQL like this: CREATE PROCEDURE test_proc(IN a int, OUT b int) LANGUAGE plpgsql AS $$ BEGIN b := a * 2; END; $$; DO $$ DECLARE _a int; _b int; BEGIN _a := 10; CALL test_proc(_a, _b); RAISE NOTICE '_a: %, _b: %', _a, _b; END $$; For a top-level direct call, you can pass whatever you want, since all OUT parameters are presented as initially NULL to the procedure code. So you could just pass NULL, as in CALL test_proc(5, NULL). The code changes to make this happen are not as significant as I had initially feared. Most of the patch is expanded documentation and additional tests. In some cases, I changed the terminology from "input parameters" to "signature parameters" to make the difference clearer. Overall, while this introduces some additional conceptual complexity, the way it works is pretty obvious in the end, and people porting from other systems will find it working as expected. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Support for OUT parameters in procedures
Robert Haas <robertmhaas@gmail.com> — 2020-08-27T13:56:47Z
On Thu, Aug 27, 2020 at 4:34 AM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > For a top-level direct call, you can pass whatever you want, since all > OUT parameters are presented as initially NULL to the procedure code. > So you could just pass NULL, as in CALL test_proc(5, NULL). Is that actually how other systems work? I would think that people would expect to pass, say, a package variable, and expect that it will get updated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-
Re: Support for OUT parameters in procedures
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2020-08-28T06:04:04Z
On 2020-08-27 15:56, Robert Haas wrote: > On Thu, Aug 27, 2020 at 4:34 AM Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: >> For a top-level direct call, you can pass whatever you want, since all >> OUT parameters are presented as initially NULL to the procedure code. >> So you could just pass NULL, as in CALL test_proc(5, NULL). > > Is that actually how other systems work? I would think that people > would expect to pass, say, a package variable, and expect that it will > get updated. The handling of results of SQL statements executed at the top level (a.k.a. direct SQL) is implementation-specific and varies widely in practice. More interesting in practice, in terms of functionality and also compatibility, are nested calls in PL/pgSQL as well as integration in JDBC. We already support INOUT parameters in procedures, so the method of returning the value of output parameters after the CALL already exists. This patch doesn't touch that at all, really. If we had or would add other places to put those results, such as package variables, then they could be added independently of this patch. Of course, feedback from those more knowledgeable in other systems than me would be welcome. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Support for OUT parameters in procedures
Robert Haas <robertmhaas@gmail.com> — 2020-08-28T13:30:46Z
On Fri, Aug 28, 2020 at 2:04 AM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > The handling of results of SQL statements executed at the top level > (a.k.a. direct SQL) is implementation-specific and varies widely in > practice. More interesting in practice, in terms of functionality and > also compatibility, are nested calls in PL/pgSQL as well as integration > in JDBC. I agree that driver integration, and in particular JDBC integration, is important and needs some thought. I don't think it horribly matters, with a feature like this, what shows up when people type stuff into psql. Whatever it is, people will get used to it. But when they interact through a driver, it's different. It is no good inventing things, either in PostgreSQL or in the JDBC driver for PostgreSQL, that make PostgreSQL behave differently from every other database they use. I don't know exactly how we get to a good outcome here, but I think it's worth some careful consideration. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-
Re: Support for OUT parameters in procedures
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> — 2020-09-28T16:43:39Z
On 8/27/20 4:34 AM, Peter Eisentraut wrote: > Procedures currently don't allow OUT parameters. The reason for this > is that at the time procedures were added (PG11), some of the details > of how this should work were unclear and the issue was postponed. I > am now intending to resolve this. > > AFAICT, OUT parameters in _functions_ are not allowed per the SQL > standard, so whatever PostgreSQL is doing there at the moment is > mostly our own invention. By contrast, I am here intending to make > OUT parameters in procedures work per SQL standard and be compatible > with the likes of PL/SQL. > > The main difference is that for procedures, OUT parameters are part of > the signature and need to be specified as part of the call. This > makes sense for nested calls in PL/pgSQL like this: > > CREATE PROCEDURE test_proc(IN a int, OUT b int) > LANGUAGE plpgsql > AS $$ > BEGIN > b := a * 2; > END; > $$; > > DO $$ > DECLARE _a int; _b int; > BEGIN > _a := 10; > CALL test_proc(_a, _b); > RAISE NOTICE '_a: %, _b: %', _a, _b; > END > $$; > > For a top-level direct call, you can pass whatever you want, since all > OUT parameters are presented as initially NULL to the procedure code. > So you could just pass NULL, as in CALL test_proc(5, NULL). > > The code changes to make this happen are not as significant as I had > initially feared. Most of the patch is expanded documentation and > additional tests. In some cases, I changed the terminology from > "input parameters" to "signature parameters" to make the difference > clearer. Overall, while this introduces some additional conceptual > complexity, the way it works is pretty obvious in the end, and people > porting from other systems will find it working as expected. > I've reviewed this, and I think it's basically fine. I've made an addition that adds a test module that shows how this can be called from libpq - that should be helpful (I hope) for driver writers. A combined patch with the original plus my test suite is attached. I think this can be marked RFC. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Support for OUT parameters in procedures
Pavel Stehule <pavel.stehule@gmail.com> — 2020-09-29T06:23:20Z
po 28. 9. 2020 v 18:43 odesílatel Andrew Dunstan < andrew.dunstan@2ndquadrant.com> napsal: > > On 8/27/20 4:34 AM, Peter Eisentraut wrote: > > Procedures currently don't allow OUT parameters. The reason for this > > is that at the time procedures were added (PG11), some of the details > > of how this should work were unclear and the issue was postponed. I > > am now intending to resolve this. > > > > AFAICT, OUT parameters in _functions_ are not allowed per the SQL > > standard, so whatever PostgreSQL is doing there at the moment is > > mostly our own invention. By contrast, I am here intending to make > > OUT parameters in procedures work per SQL standard and be compatible > > with the likes of PL/SQL. > > > > The main difference is that for procedures, OUT parameters are part of > > the signature and need to be specified as part of the call. This > > makes sense for nested calls in PL/pgSQL like this: > > > > CREATE PROCEDURE test_proc(IN a int, OUT b int) > > LANGUAGE plpgsql > > AS $$ > > BEGIN > > b := a * 2; > > END; > > $$; > > > > DO $$ > > DECLARE _a int; _b int; > > BEGIN > > _a := 10; > > CALL test_proc(_a, _b); > > RAISE NOTICE '_a: %, _b: %', _a, _b; > > END > > $$; > > > > For a top-level direct call, you can pass whatever you want, since all > > OUT parameters are presented as initially NULL to the procedure code. > > So you could just pass NULL, as in CALL test_proc(5, NULL). > This was an important issue if I remember well. Passing mandatory NULL as OUT arguments solves this issue. I fully agree so OUT arguments are part of the procedure's signature. Unfortunately, there is another difference from functions, but I don't think so there is a better solution, and we should live with it. I think it can work well. > > > The code changes to make this happen are not as significant as I had > > initially feared. Most of the patch is expanded documentation and > > additional tests. In some cases, I changed the terminology from > > "input parameters" to "signature parameters" to make the difference > > clearer. Overall, while this introduces some additional conceptual > > complexity, the way it works is pretty obvious in the end, and people > > porting from other systems will find it working as expected. > > > > > I've reviewed this, and I think it's basically fine. I've made an > addition that adds a test module that shows how this can be called from > libpq - that should be helpful (I hope) for driver writers. > > > A combined patch with the original plus my test suite is attached. > > I found one issue. The routine for selecting function or procedure based on signature should be fixed. CREATE OR REPLACE PROCEDURE public.procp(OUT integer) LANGUAGE plpgsql AS $procedure$ BEGIN $1 := 10; END; $procedure$ DO $$ DECLARE n numeric; BEGIN CALL procp(n); RAISE NOTICE '%', n; END; $$; ERROR: procedure procp(numeric) does not exist LINE 1: CALL procp(n) ^ HINT: No procedure matches the given name and argument types. You might need to add explicit type casts. QUERY: CALL procp(n) CONTEXT: PL/pgSQL function inline_code_block line 4 at CALL I think this example should work. But it doesn't work now for INOUT, and this fix will not be easy, so it should be solved as a separate issue. This features are complete and useful now, and it can be fixed later without problems with compatibility issues. Another issue are using polymorphic arguments postgres=# create or replace procedure px(anyelement, out anyelement) as $$ begin $2 := $1; end; $$ language plpgsql; postgres=# call px(10, null); ERROR: cannot display a value of type anyelement but inside plpgsql it works do $$ declare xx int; begin call px(10, xx); raise notice '%', xx; end; $$; > I think this can be marked RFC. > +1 Pavel > > cheers > > > andrew > > > > -- > Andrew Dunstan https://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > -
Re: Support for OUT parameters in procedures
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2020-10-05T09:46:02Z
On 2020-09-29 08:23, Pavel Stehule wrote: > This was an important issue if I remember well. Passing mandatory NULL > as OUT arguments solves this issue. > I fully agree so OUT arguments are part of the procedure's signature. > Unfortunately, there is another difference > from functions, but I don't think so there is a better solution, and we > should live with it. I think it can work well. This has been committed. > I found one issue. The routine for selecting function or procedure based > on signature should be fixed. > > CREATE OR REPLACE PROCEDURE public.procp(OUT integer) > LANGUAGE plpgsql > AS $procedure$ > BEGIN > $1 := 10; > END; > $procedure$ > > DO > $$ > DECLARE n numeric; > BEGIN > CALL procp(n); > RAISE NOTICE '%', n; > END; > $$; > ERROR: procedure procp(numeric) does not exist > LINE 1: CALL procp(n) > ^ > HINT: No procedure matches the given name and argument types. You might > need to add explicit type casts. > QUERY: CALL procp(n) > CONTEXT: PL/pgSQL function inline_code_block line 4 at CALL This is normal; there is no implicit cast from numeric to int. The same error happens if you call a function foo(int) with foo(42::numeric). > postgres=# create or replace procedure px(anyelement, out anyelement) > as $$ > begin > $2 := $1; > end; > $$ language plpgsql; > > postgres=# call px(10, null); > ERROR: cannot display a value of type anyelement > > but inside plpgsql it works > do $$ > declare xx int; > begin > call px(10, xx); > raise notice '%', xx; > end; > $$; This might be worth further investigation, but since it happens also with INOUT parameters, it seems orthogonal to this patch. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Fwd: Support for OUT parameters in procedures
Dave Cramer <davecramer@postgres.rocks> — 2020-10-05T10:54:23Z
Just saw this on hackers. Anyon care to comment ? Dave Cramer www.postgres.rocks ---------- Forwarded message --------- From: Robert Haas <robertmhaas@gmail.com> Date: Fri, 28 Aug 2020 at 09:31 Subject: Re: Support for OUT parameters in procedures To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> Cc: pgsql-hackers <pgsql-hackers@postgresql.org> On Fri, Aug 28, 2020 at 2:04 AM Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote: > The handling of results of SQL statements executed at the top level > (a.k.a. direct SQL) is implementation-specific and varies widely in > practice. More interesting in practice, in terms of functionality and > also compatibility, are nested calls in PL/pgSQL as well as integration > in JDBC. I agree that driver integration, and in particular JDBC integration, is important and needs some thought. I don't think it horribly matters, with a feature like this, what shows up when people type stuff into psql. Whatever it is, people will get used to it. But when they interact through a driver, it's different. It is no good inventing things, either in PostgreSQL or in the JDBC driver for PostgreSQL, that make PostgreSQL behave differently from every other database they use. I don't know exactly how we get to a good outcome here, but I think it's worth some careful consideration. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-
Re: Support for OUT parameters in procedures
Pavel Stehule <pavel.stehule@gmail.com> — 2020-10-05T11:39:05Z
po 5. 10. 2020 v 11:46 odesílatel Peter Eisentraut < peter.eisentraut@2ndquadrant.com> napsal: > On 2020-09-29 08:23, Pavel Stehule wrote: > > This was an important issue if I remember well. Passing mandatory NULL > > as OUT arguments solves this issue. > > I fully agree so OUT arguments are part of the procedure's signature. > > Unfortunately, there is another difference > > from functions, but I don't think so there is a better solution, and we > > should live with it. I think it can work well. > > This has been committed. > > > I found one issue. The routine for selecting function or procedure based > > on signature should be fixed. > > > > CREATE OR REPLACE PROCEDURE public.procp(OUT integer) > > LANGUAGE plpgsql > > AS $procedure$ > > BEGIN > > $1 := 10; > > END; > > $procedure$ > > > > DO > > $$ > > DECLARE n numeric; > > BEGIN > > CALL procp(n); > > RAISE NOTICE '%', n; > > END; > > $$; > > ERROR: procedure procp(numeric) does not exist > > LINE 1: CALL procp(n) > > ^ > > HINT: No procedure matches the given name and argument types. You might > > need to add explicit type casts. > > QUERY: CALL procp(n) > > CONTEXT: PL/pgSQL function inline_code_block line 4 at CALL > > This is normal; there is no implicit cast from numeric to int. The same > error happens if you call a function foo(int) with foo(42::numeric). > this is OUT argument - so direction is reversed - and implicit cast from int to numeric exists. > > postgres=# create or replace procedure px(anyelement, out anyelement) > > as $$ > > begin > > $2 := $1; > > end; > > $$ language plpgsql; > > > > postgres=# call px(10, null); > > ERROR: cannot display a value of type anyelement > > > > but inside plpgsql it works > > do $$ > > declare xx int; > > begin > > call px(10, xx); > > raise notice '%', xx; > > end; > > $$; > > This might be worth further investigation, but since it happens also > with INOUT parameters, it seems orthogonal to this patch. > yes - this breaks using varchar against text argument, although these types are almost identical. > -- > Peter Eisentraut http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
-
Re: Support for OUT parameters in procedures
Steven Schlansker <stevenschlansker@gmail.com> — 2020-10-05T16:16:54Z
Jdbi got a feature request for such parameters a while back: https://github.com/jdbi/jdbi/issues/1606 The user uses Oracle which I don't really care to install. When I tried to implement the feature using Postgres, I found the driver support too lacking to proceed. So there's some interest out there in making it work, and I can volunteer to at least smoke test it with my test cases... On Mon, Oct 5, 2020 at 3:54 AM Dave Cramer <davecramer@postgres.rocks> wrote: > Just saw this on hackers. Anyon care to comment ? > > Dave Cramer > www.postgres.rocks > > > ---------- Forwarded message --------- > From: Robert Haas <robertmhaas@gmail.com> > Date: Fri, 28 Aug 2020 at 09:31 > Subject: Re: Support for OUT parameters in procedures > To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> > Cc: pgsql-hackers <pgsql-hackers@postgresql.org> > > > On Fri, Aug 28, 2020 at 2:04 AM Peter Eisentraut > <peter.eisentraut@2ndquadrant.com> wrote: > > The handling of results of SQL statements executed at the top level > > (a.k.a. direct SQL) is implementation-specific and varies widely in > > practice. More interesting in practice, in terms of functionality and > > also compatibility, are nested calls in PL/pgSQL as well as integration > > in JDBC. > > I agree that driver integration, and in particular JDBC integration, > is important and needs some thought. I don't think it horribly > matters, with a feature like this, what shows up when people type > stuff into psql. Whatever it is, people will get used to it. But when > they interact through a driver, it's different. It is no good > inventing things, either in PostgreSQL or in the JDBC driver for > PostgreSQL, that make PostgreSQL behave differently from every other > database they use. I don't know exactly how we get to a good outcome > here, but I think it's worth some careful consideration. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > >
-
Re: Support for OUT parameters in procedures
Dave Cramer <davecramer@postgres.rocks> — 2020-10-05T16:24:15Z
On Mon, 5 Oct 2020 at 12:17, Steven Schlansker <stevenschlansker@gmail.com> wrote: > Jdbi got a feature request for such parameters a while back: > https://github.com/jdbi/jdbi/issues/1606 > > The user uses Oracle which I don't really care to install. When I tried > to implement the feature using Postgres, > I found the driver support too lacking to proceed. > > So there's some interest out there in making it work, and I can volunteer > to at least smoke test it with my test cases... > Sure, lets see how broken it is right now. Dave Cramer www.postgres.rocks > > On Mon, Oct 5, 2020 at 3:54 AM Dave Cramer <davecramer@postgres.rocks> > wrote: > >> Just saw this on hackers. Anyon care to comment ? >> >> Dave Cramer >> www.postgres.rocks >> >> >> ---------- Forwarded message --------- >> From: Robert Haas <robertmhaas@gmail.com> >> Date: Fri, 28 Aug 2020 at 09:31 >> Subject: Re: Support for OUT parameters in procedures >> To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com> >> Cc: pgsql-hackers <pgsql-hackers@postgresql.org> >> >> >> On Fri, Aug 28, 2020 at 2:04 AM Peter Eisentraut >> <peter.eisentraut@2ndquadrant.com> wrote: >> > The handling of results of SQL statements executed at the top level >> > (a.k.a. direct SQL) is implementation-specific and varies widely in >> > practice. More interesting in practice, in terms of functionality and >> > also compatibility, are nested calls in PL/pgSQL as well as integration >> > in JDBC. >> >> I agree that driver integration, and in particular JDBC integration, >> is important and needs some thought. I don't think it horribly >> matters, with a feature like this, what shows up when people type >> stuff into psql. Whatever it is, people will get used to it. But when >> they interact through a driver, it's different. It is no good >> inventing things, either in PostgreSQL or in the JDBC driver for >> PostgreSQL, that make PostgreSQL behave differently from every other >> database they use. I don't know exactly how we get to a good outcome >> here, but I think it's worth some careful consideration. >> >> -- >> Robert Haas >> EnterpriseDB: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> >> >>
-
Re: Support for OUT parameters in procedures
Andrew Dunstan <andrew@dunslane.net> — 2020-10-05T18:59:32Z
On 10/5/20 12:24 PM, Dave Cramer wrote: > > > On Mon, 5 Oct 2020 at 12:17, Steven Schlansker > <stevenschlansker@gmail.com <mailto:stevenschlansker@gmail.com>> wrote: > > Jdbi got a feature request for such parameters a while back: > https://github.com/jdbi/jdbi/issues/1606 > > The user uses Oracle which I don't really care to install. When I > tried to implement the feature using Postgres, > I found the driver support too lacking to proceed. > > So there's some interest out there in making it work, and I can > volunteer to at least smoke test it with my test cases... > > > Sure, lets see how broken it is right now. > > > We're working on it. It's a bit tricky, but we need to get it working, for sure. The main thing is that the driver needs to send some type other than VOID for the OUT param. Minimally that can be UNKNOWN, but it should probably reflect the type set in registerOutParameter(). cheers andrew -- Andrew Dunstan EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
-
Re: Support for OUT parameters in procedures
Dave Cramer <davecramer@postgres.rocks> — 2020-10-05T22:21:21Z
On Mon, 5 Oct 2020 at 14:59, Andrew Dunstan <andrew@dunslane.net> wrote: > > On 10/5/20 12:24 PM, Dave Cramer wrote: > > > > > > On Mon, 5 Oct 2020 at 12:17, Steven Schlansker > > <stevenschlansker@gmail.com <mailto:stevenschlansker@gmail.com>> wrote: > > > > Jdbi got a feature request for such parameters a while back: > > https://github.com/jdbi/jdbi/issues/1606 > > > > The user uses Oracle which I don't really care to install. When I > > tried to implement the feature using Postgres, > > I found the driver support too lacking to proceed. > > > > So there's some interest out there in making it work, and I can > > volunteer to at least smoke test it with my test cases... > > > > > > Sure, lets see how broken it is right now. > > > > > > > > We're working on it. It's a bit tricky, but we need to get it working, > for sure. The main thing is that the driver needs to send some type > other than VOID for the OUT param. Minimally that can be UNKNOWN, but it > should probably reflect the type set in registerOutParameter(). > > I would think we run into the normal issues with things like timestamps and dates with and without time zones Thanks, Dave Cramer www.postgres.rocks