Обсуждение: 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();