How to handle nested record data.

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

Thanks for you quick reply, but i need to take this returned row in another
pgsql function and do further processing.

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

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