pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql out parameter with select into
9+ messages / 5 participants
[nested] [flat]
* plpgsql out parameter with select into
@ 2010-08-17 18:45 Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Horváth Imre @ 2010-08-17 18:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
@ 2010-08-18 05:42 ` Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 14:45 ` Re: plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 15:00 ` Re: plpgsql out parameter with select into Imre Horvath <blemidon@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Sergey Konoplev @ 2010-08-18 05:42 UTC (permalink / raw)
To: Horváth Imre <horvath.imre@blemidon.hu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
@ 2010-08-18 14:45 ` Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 21:58 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Horváth Imre @ 2010-08-18 14:45 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-sql
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
> >
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 14:45 ` Re: plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
@ 2010-08-18 21:58 ` Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 22:09 ` Re: plpgsql out parameter with select into Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Sergey Konoplev @ 2010-08-18 21:58 UTC (permalink / raw)
To: Horváth Imre <horvath.imre@blemidon.hu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 14:45 ` Re: plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 21:58 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
@ 2010-08-18 22:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-18 22:18 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2010-08-18 22:09 UTC (permalink / raw)
To: Horváth Imre <horvath.imre@blemidon.hu>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 14:45 ` Re: plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 21:58 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 22:09 ` Re: plpgsql out parameter with select into Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-08-18 22:18 ` Sergey Konoplev <gray.ru@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Sergey Konoplev @ 2010-08-18 22:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Horváth Imre <horvath.imre@blemidon.hu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Re: plpgsql out parameter with select into Sergey Konoplev <gray.ru@gmail.com>
@ 2010-08-18 15:00 ` Imre Horvath <blemidon@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Imre Horvath @ 2010-08-18 15:00 UTC (permalink / raw)
To: Sergey Konoplev <gray.ru@gmail.com>; +Cc: pgsql-sql
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
> >
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* plpgsql out parameter with select into
@ 2010-08-17 18:46 Imre Horvath <blemidon@gmail.com>
2010-08-18 04:39 ` Re: plpgsql out parameter with select into Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Imre Horvath @ 2010-08-17 18:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: plpgsql out parameter with select into
2010-08-17 18:46 plpgsql out parameter with select into Imre Horvath <blemidon@gmail.com>
@ 2010-08-18 04:39 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Pavel Stehule @ 2010-08-18 04:39 UTC (permalink / raw)
To: Imre Horvath <blemidon@gmail.com>; +Cc: pgsql-sql
Hello
It cannot work, you mix the sql with plpgsql language
2010/8/17 Imre Horvath <blemidon@gmail.com>:
> 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
>
>
create or replace function test1(out _status integer) returns integer
as $$ begin _status := 10; end; $$ language plpgsql;
create or replace function test3() returns void as $$ declare status
integer; begin select into status _status from test1(); raise notice
'%', status; end; $$ language plpgsql;
this working for me.
postgres=# select test3();
NOTICE: 10
test3
───────
(1 row)
Regards
Pavel Stehule
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-08-18 22:18 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-17 18:45 plpgsql out parameter with select into Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 05:42 ` Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 14:45 ` Horváth Imre <horvath.imre@blemidon.hu>
2010-08-18 21:58 ` Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 22:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-18 22:18 ` Sergey Konoplev <gray.ru@gmail.com>
2010-08-18 15:00 ` Imre Horvath <blemidon@gmail.com>
2010-08-17 18:46 plpgsql out parameter with select into Imre Horvath <blemidon@gmail.com>
2010-08-18 04:39 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox