Reading an OUT parameter out of a function call

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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();

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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();

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general