Обсуждение: Reading an OUT parameter out of a function call
Hi, I have a simple void function: CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN pnr := 1; END; $$ LANGUAGE plpgsql; How do I access myparam? I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or inside another function? Yours, Stefan
On Mon, Feb 25, 2013 at 11:22 AM, Stefan Keller <sfkeller@gmail.com> wrote: > Hi, > > I have a simple void function: > > CREATE OR REPLACE FUNCTION myfn(myparam OUT int) > AS $$ > BEGIN > pnr := 1; > END; > $$ LANGUAGE plpgsql; > > How do I access myparam? > I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; > Or inside another function? that should work. what error are you getting? also, SELECT myparam FROM myfn(); merlin
Hello 2013/2/25 Stefan Keller <sfkeller@gmail.com>: > Hi, > > I have a simple void function: > > CREATE OR REPLACE FUNCTION myfn(myparam OUT int) > AS $$ > BEGIN > pnr := 1; > END; > $$ LANGUAGE plpgsql; > > How do I access myparam? > I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; > Or inside another function? > you cannot access to out parameters outside function - because they doesn't exist - postgresql cannot pass parameters by ref. your example is exactly same as int returning function - you can use it in plpgsql variable := myfn(); -- variable is scalar int type if function has more out parameters, then return type is record type. CREATE OR REPLACE FUNCTION public.f1(a integer, b integer, OUT c integer, OUT d integer) RETURNS record LANGUAGE plpgsql AS $function$ begin c := a + b; d := c * 2; end; $function$ postgres=# select f1(10,20); f1 --------- (30,60) (1 row) postgres=# select * from f1(10,20); c | d ----+---- 30 | 60 (1 row) create or replace function foo() returns void as $$ declare r record; begin r := f1(10,20); raise warning 'c=%, d=%', r.c, r.d; end; $$ language plpgsql; CREATE FUNCTION postgres=# select foo(); WARNING: 01000: c=30, d=60 foo ----- (1 row) Regards Pavel Stehule > Yours, Stefan > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 02/25/2013 09:22 AM, Stefan Keller wrote: > Hi, > > I have a simple void function: > > CREATE OR REPLACE FUNCTION myfn(myparam OUT int) > AS $$ > BEGIN > pnr := 1; > END; > $$ LANGUAGE plpgsql; > > How do I access myparam? > I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; > Or inside another function? You get the above to load? I get: ERROR: "pnr" is not a known variable LINE 4: pnr := 1; > > Yours, Stefan > > -- Adrian Klaver adrian.klaver@gmail.com
PiA+IEkgaGF2ZSBhIHNpbXBsZSB2b2lkIGZ1bmN0aW9uOg0KPiA+DQo+ID4gQ1JFQVRFIE9SIFJF UExBQ0UgRlVOQ1RJT04gbXlmbihteXBhcmFtIE9VVCBpbnQpIEFTICQkIEJFR0lODQo+ID4gICAg cG5yIDo9IDE7DQo+ID4gRU5EOw0KPiA+ICQkIExBTkdVQUdFIHBscGdzcWw7DQo+ID4NCj4gPiBI b3cgZG8gSSBhY2Nlc3MgbXlwYXJhbT8NCj4gPiBJIHRob3VnaHQgdGhpcyBzaG91bGQgd29yayB3 aXRoIDkuMS85LjI6IFNFTEVDVCAobXlmbigpKS5teXBhcmFtOyBPciANCj4gPiBpbnNpZGUgYW5v dGhlciBmdW5jdGlvbj8NCj4NCj4gWW91IGdldCB0aGUgYWJvdmUgdG8gbG9hZD8NCj4gSSBnZXQ6 DQo+IEVSUk9SOiAgInBuciIgaXMgbm90IGEga25vd24gdmFyaWFibGUNCj4gTElORSA0OiAgIHBu ciA6PSAxOw0KDQpUaGUgZm9sbG93aW5nIGFsbCB3b3JrcyB1bmRlciA5LjI6DQoNCkNSRUFURSBP UiBSRVBMQUNFIEZVTkNUSU9OIG15Zm4obXlwYXJhbSBPVVQgaW50LCBteXBhcmFtMiBPVVQgaW50 KSBBUyAkJCBCRUdJTg0KCW15cGFyYW0gOj0gMTsNCglteXBhcmFtMiA6PSAyOw0KRU5EOw0KJCQg TEFOR1VBR0UgcGxwZ3NxbDsNCg0KDQpzZWxlY3QgbXlwYXJhbSBmcm9tIG15Zm4oKTsNCnNlbGVj dCBteXBhcmFtMiBmcm9tIG15Zm4oKTsNCnNlbGVjdCBteWZuKCk7DQpzZWxlY3QgKiBmcm9tIG15 Zm4oKTsNCg==
Thank you Keane and all. That works for me too. Yours, Stefan 2013/2/25 Russell Keane <Russell.Keane@inps.co.uk>: >> > I have a simple void function: >> > >> > CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN >> > pnr := 1; >> > END; >> > $$ LANGUAGE plpgsql; >> > >> > How do I access myparam? >> > I thought this should work with 9.1/9.2: SELECT (myfn()).myparam; Or >> > inside another function? >> >> You get the above to load? >> I get: >> ERROR: "pnr" is not a known variable >> LINE 4: pnr := 1; > > The following all works under 9.2: > > CREATE OR REPLACE FUNCTION myfn(myparam OUT int, myparam2 OUT int) AS $$ BEGIN > myparam := 1; > myparam2 := 2; > END; > $$ LANGUAGE plpgsql; > > > select myparam from myfn(); > select myparam2 from myfn(); > select myfn(); > select * from myfn();