Thread: plpgsql out parameter with select into

plpgsql out parameter with select into

From

Horváth Imre

Date:

18 August 2010, 05:28:07

Hi!

My question is, how can I get the out parameter from a function with
SELECT INTO by name?
I mean:

create function testfunc1(OUT _status integer) returns integer as
$BODY$_status := 0;
$BODY$
language plpgsql;

create function testfunc2() as
declarestatus integer;
$BODY$select into status * from testfunc1();
$BODY$
language plpgsql;

create function testfunc3() as
declarestatus integer;
$BODY$select into status _status from testfunc1();
$BODY$
language plpgsql;

testfunc2 works, testfunc3 not.

Thanks in advance:
Imre Horvath

Re: plpgsql out parameter with select into

From

Sergey Konoplev

Date:

18 August 2010, 05:43:20

Hi,

SELECT column_name
INTO var_name
FROM ...

2010/8/17 Horváth Imre <horvath.imre@blemidon.hu>:
> Hi!
>
> My question is, how can I get the out parameter from a function with
> SELECT INTO by name?
> I mean:
>
> create function testfunc1(OUT _status integer) returns integer as
> $BODY$
>        _status := 0;
> $BODY$
> language plpgsql;
>
> create function testfunc2() as
> declare
>        status integer;
> $BODY$
>        select into status * from testfunc1();
> $BODY$
> language plpgsql;
>
> create function testfunc3() as
> declare
>        status integer;
> $BODY$
>        select into status _status from testfunc1();
> $BODY$
> language plpgsql;
>
> testfunc2 works, testfunc3 not.
>
> Thanks in advance:
> Imre Horvath
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

Re: plpgsql out parameter with select into

From

Horváth Imre

Date:

18 August 2010, 14:58:19

It don't work...

Only select into status * from... works.

Imre Horvath

2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta:
> Hi,
> 
> SELECT column_name
> INTO var_name
> FROM ...
> 
> 2010/8/17 Horváth Imre <horvath.imre@blemidon.hu>:
> > Hi!
> >
> > My question is, how can I get the out parameter from a function with
> > SELECT INTO by name?
> > I mean:
> >
> > create function testfunc1(OUT _status integer) returns integer as
> > $BODY$
> >        _status := 0;
> > $BODY$
> > language plpgsql;
> >
> > create function testfunc2() as
> > declare
> >        status integer;
> > $BODY$
> >        select into status * from testfunc1();
> > $BODY$
> > language plpgsql;
> >
> > create function testfunc3() as
> > declare
> >        status integer;
> > $BODY$
> >        select into status _status from testfunc1();
> > $BODY$
> > language plpgsql;
> >
> > testfunc2 works, testfunc3 not.
> >
> > Thanks in advance:
> > Imre Horvath
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> >
> 
> 
>

Re: plpgsql out parameter with select into

From

Imre Horvath

Date:

18 August 2010, 15:00:31

It don't work...       
Only select into status * from... works.       
Imre Horvath

2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta:
> Hi,
> 
> SELECT column_name
> INTO var_name
> FROM ...
> 
> 2010/8/17 Horváth Imre <horvath.imre@blemidon.hu>:
> > Hi!
> >
> > My question is, how can I get the out parameter from a function with
> > SELECT INTO by name?
> > I mean:
> >
> > create function testfunc1(OUT _status integer) returns integer as
> > $BODY$
> >        _status := 0;
> > $BODY$
> > language plpgsql;
> >
> > create function testfunc2() as
> > declare
> >        status integer;
> > $BODY$
> >        select into status * from testfunc1();
> > $BODY$
> > language plpgsql;
> >
> > create function testfunc3() as
> > declare
> >        status integer;
> > $BODY$
> >        select into status _status from testfunc1();
> > $BODY$
> > language plpgsql;
> >
> > testfunc2 works, testfunc3 not.
> >
> > Thanks in advance:
> > Imre Horvath
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> >
> 
> 
>

Re: plpgsql out parameter with select into

From

Sergey Konoplev

Date:

18 August 2010, 21:58:43

What is your PG version?

2010/8/18 Horváth Imre <horvath.imre@blemidon.hu>:
> It don't work...
>
> Only select into status * from... works.
>
> Imre Horvath
>
> 2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta:
>> Hi,
>>
>> SELECT column_name
>> INTO var_name
>> FROM ...
>>
>> 2010/8/17 Horváth Imre <horvath.imre@blemidon.hu>:
>> > Hi!
>> >
>> > My question is, how can I get the out parameter from a function with
>> > SELECT INTO by name?
>> > I mean:
>> >
>> > create function testfunc1(OUT _status integer) returns integer as
>> > $BODY$
>> >        _status := 0;
>> > $BODY$
>> > language plpgsql;
>> >
>> > create function testfunc2() as
>> > declare
>> >        status integer;
>> > $BODY$
>> >        select into status * from testfunc1();
>> > $BODY$
>> > language plpgsql;
>> >
>> > create function testfunc3() as
>> > declare
>> >        status integer;
>> > $BODY$
>> >        select into status _status from testfunc1();
>> > $BODY$
>> > language plpgsql;
>> >
>> > testfunc2 works, testfunc3 not.
>> >
>> > Thanks in advance:
>> > Imre Horvath
>> >
>> >
>> > --
>> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-sql
>> >
>>
>>
>>
>
>
>



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802

Re: plpgsql out parameter with select into

From

Tom Lane

Date:

18 August 2010, 22:10:22

> 2010/8/18 Horv�th Imre <horvath.imre@blemidon.hu>:
>> It don't work...

A function returning a scalar type cannot control the column name
assigned to the scalar in the calling query.  To do that, you need to
return a composite type, which means there need to be at least two OUT
parameters if you're trying to do it via OUT-parameter names.
        regards, tom lane

Re: plpgsql out parameter with select into

From

Sergey Konoplev

Date:

18 August 2010, 22:18:42

2010/8/19 Tom Lane <tgl@sss.pgh.pa.us>:
>> 2010/8/18 Horváth Imre <horvath.imre@blemidon.hu>:
>>> It don't work...
>
> A function returning a scalar type cannot control the column name
> assigned to the scalar in the calling query.  To do that, you need to
> return a composite type, which means there need to be at least two OUT
> parameters if you're trying to do it via OUT-parameter names.

So we could do either

SELECT funcname INTO varname FROM funcname()

or

SELECT * INTO varname FROM funcname();

or

SELECT funcname() INTO varname

correct?


>
>                        regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802