From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | yi huang <yi(dot)codeplayer(at)gmail(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to handle nested record data. |
Date: | 2012-05-30 10:12:52 |
Message-ID: | CAL_0b1uYKo+wXm=MqfZ7DM+-iak_sFLz_0npQqVBSsJkEXxj6A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, May 30, 2012 at 9:03 AM, yi huang <yi(dot)codeplayer(at)gmail(dot)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(dot)codeplayer(at)gmail(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2012-05-30 12:45:02 | Re: Updateable Views or Synonyms. |
Previous Message | Alban Hertroys | 2012-05-30 07:08:50 | Re: Updateable Views or Synonyms. |