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/