Thread: How to handle nested record data.
I'm porting a oracle function to postgresql, which has signature like this:
FUNCTION foo
( seq IN varchar
, somerow OUT SomeTable
, otherinfo OUT varchar
)
It's easy to port this function itself to postgresql, but i have problem to execute this function and assign the results into variables:
SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
It complains v_somerow can not be row type.
How to handle the result of function foo?
Best regards.
YiHuang.
Hello create or replace function call_foo() returns void as $$ declare r record; begin r := foo('Hello'); raise notice ''% %', r.somerow, r.otherinfo; end; $$ language plpgsql; regards Pavel 2012/5/30 yi huang <yi.codeplayer@gmail.com>: > I'm porting a oracle function to postgresql, which has signature like this: > > FUNCTION foo > ( seq IN varchar > , somerow OUT SomeTable > , otherinfo OUT varchar > ) > > It's easy to port this function itself to postgresql, but i have problem to > execute this function and assign the results into variables: > > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo); > > It complains v_somerow can not be row type. > > How to handle the result of function foo? > > Best regards. > YiHuang.
Thanks for you quick reply, but i need to take this returned row in another pgsql function and do further processing.
--
http://yi-programmer.com/
On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
create or replace function call_foo()
returns void as $$
declare r record;
begin
r := foo('Hello');
raise notice ''% %', r.somerow, r.otherinfo;
end;
$$ language plpgsql;
regards
Pavel
2012/5/30 yi huang <yi.codeplayer@gmail.com>:> I'm porting a oracle function to postgresql, which has signature like this:
>
> FUNCTION foo
> ( seq IN varchar
> , somerow OUT SomeTable
> , otherinfo OUT varchar
> )
>
> It's easy to port this function itself to postgresql, but i have problem to
> execute this function and assign the results into variables:
>
> SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
>
> It complains v_somerow can not be row type.
>
> How to handle the result of function foo?
>
> Best regards.
> YiHuang.
http://yi-programmer.com/
On Wed, May 30, 2012 at 12:36 PM, yi huang <yi.codeplayer@gmail.com> wrote:
Thanks for you quick reply, but i need to take this returned row in another pgsql function and do further processing.
Sorry, i was misunderstood, i get it now, just use a record type, thanks very much.
--On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:Hello
create or replace function call_foo()
returns void as $$
declare r record;
begin
r := foo('Hello');
raise notice ''% %', r.somerow, r.otherinfo;
end;
$$ language plpgsql;
regards
Pavel
2012/5/30 yi huang <yi.codeplayer@gmail.com>:> I'm porting a oracle function to postgresql, which has signature like this:
>
> FUNCTION foo
> ( seq IN varchar
> , somerow OUT SomeTable
> , otherinfo OUT varchar
> )
>
> It's easy to port this function itself to postgresql, but i have problem to
> execute this function and assign the results into variables:
>
> SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
>
> It complains v_somerow can not be row type.
>
> How to handle the result of function foo?
>
> Best regards.
> YiHuang.
http://yi-programmer.com/
http://yi-programmer.com/
On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
create or replace function call_foo()
returns void as $$
declare r record;
begin
r := foo('Hello');
raise notice ''% %', r.somerow, r.otherinfo;
end;
$$ language plpgsql;
It turns out i also need to define a type for the result record of `foo`, because record can't reveal the structure of the result (it complains: record "r" has no field "somerow").
I have to created this type:
create type foo_result as (somerow SomeTable, otherinfo varchar);
then change `r record;` to `r foo_result;` , no need change `foo` itself, and it works now.
I don't know is this the best way to do this though.
regards
Pavel
2012/5/30 yi huang <yi.codeplayer@gmail.com>:> I'm porting a oracle function to postgresql, which has signature like this:
>
> FUNCTION foo
> ( seq IN varchar
> , somerow OUT SomeTable
> , otherinfo OUT varchar
> )
>
> It's easy to port this function itself to postgresql, but i have problem to
> execute this function and assign the results into variables:
>
> SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
>
> It complains v_somerow can not be row type.
>
> How to handle the result of function foo?
>
> Best regards.
> YiHuang.
http://yi-programmer.com/
2012/5/30 yi huang <yi.codeplayer@gmail.com>: > On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule <pavel.stehule@gmail.com> > wrote: >> >> Hello >> >> create or replace function call_foo() >> returns void as $$ >> declare r record; >> begin >> r := foo('Hello'); >> raise notice ''% %', r.somerow, r.otherinfo; >> end; >> $$ language plpgsql; > > > It turns out i also need to define a type for the result record of `foo`, > because record can't reveal the structure of the result (it complains: > record "r" has no field "somerow"). > I have to created this type: > > create type foo_result as (somerow SomeTable, otherinfo varchar); > > then change `r record;` to `r foo_result;` , no need change `foo` itself, > and it works now. > > I don't know is this the best way to do this though. best way is way that works :). Implementation of records and related features is relative complex and not consistent all time - mainly when function with OUT arguments is used. There are a issues - sometimes cast is necessary, sometimes nested records can be accessed only to first level (and for second levels you needs casts or auxiliary variables) - so it works perfectly, but sometime is difficult to find syntax that works. Regards Pavel > >> >> >> regards >> >> Pavel >> >> 2012/5/30 yi huang <yi.codeplayer@gmail.com>: >> > I'm porting a oracle function to postgresql, which has signature like >> > this: >> > >> > FUNCTION foo >> > ( seq IN varchar >> > , somerow OUT SomeTable >> > , otherinfo OUT varchar >> > ) >> > >> > It's easy to port this function itself to postgresql, but i have problem >> > to >> > execute this function and assign the results into variables: >> > >> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo); >> > >> > It complains v_somerow can not be row type. >> > >> > How to handle the result of function foo? >> > >> > Best regards. >> > YiHuang. > > > > > -- > http://yi-programmer.com/
On Wed, May 30, 2012 at 9:03 AM, yi huang <yi.codeplayer@gmail.com> wrote: > It turns out i also need to define a type for the result record of `foo`, > because record can't reveal the structure of the result (it complains: > record "r" has no field "somerow"). > I have to created this type: > > create type foo_result as (somerow SomeTable, otherinfo varchar); > > then change `r record;` to `r foo_result;` , no need change `foo` itself, > and it works now. > > I don't know is this the best way to do this though. I am not sure my assumption is correct but have not you tried to do it like this SELECT * FROM foo(seq) AS (somerow SomeTable, otherinfo varchar) it will probably help to avoid creation of a separate type for the function result. > >> >> >> regards >> >> Pavel >> >> 2012/5/30 yi huang <yi.codeplayer@gmail.com>: >> > I'm porting a oracle function to postgresql, which has signature like >> > this: >> > >> > FUNCTION foo >> > ( seq IN varchar >> > , somerow OUT SomeTable >> > , otherinfo OUT varchar >> > ) >> > >> > It's easy to port this function itself to postgresql, but i have problem >> > to >> > execute this function and assign the results into variables: >> > >> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo); >> > >> > It complains v_somerow can not be row type. >> > >> > How to handle the result of function foo? >> > >> > Best regards. >> > YiHuang. > > > > > -- > http://yi-programmer.com/ -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204