From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | thomas veymont <thomas(dot)veymont(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: plpgsql : adding record variable to table |
Date: | 2012-04-19 11:58:13 |
Message-ID: | CAFj8pRDpCEQLzN1G3tToSE92Q8kR1x1=aKUjVy4BgVec=-OtDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
2012/4/19 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>:
> that made it, thank you.
> For other readers, here is what I finally did :
>
> CREATE TABLE mytable (...)
>
> CREATE FUNCTION xxxx (...) RETURNS SETOF mytable AS $$
> DECLARE
> r mytable%rowtype
> BEGIN
> ...
> FOR r IN select * from mytable
> LOOP
> ....
> RETURN next r;
> END LOOP;
> RETURN;
> END;
>
> I don't know if %rowtype is actually needed. I found this in here :
> http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions
>
%rowtype is not required - in pg (it is syntax from Oracle), but it is
good to use it to increase readability.
Regards
Pavel
> thanks again
> Tom
>
>
> 2012/4/19 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2012/4/19 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>:
>>> hi Pavel,
>>>
>>> thanks for your answer,
>>>
>>
>>> I don't understand exactly how "y" should be declared, and how it
>>> should be returned by the function (as a table,
>>> as a "set of record", or maybe as some kind of generic object, I don't
>>> know exactly what's possible with pl/psql.).
>>>
>>
>> r must used predeclared type - declared type or table. It doesn't work
>> with "record" type.
>>
>> Any table specifies composite type too:
>>
>> create table y(a int, b int);
>>
>> create or replace function foo()
>> returns setof y as $$
>> declare r y;
>> begin
>> for r in select * from y
>> loop
>> return next r;
>> end loop;
>> return;
>> end;
>>
>> you can declare composite type via command CREATE TYPE
>>
>> create type y as (a int, b int)
>>
>> Regards
>>
>> Pavel Stehule
>>
>>> cheers
>>> Tom
>>>
>>> 2012/4/18 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>> Hello
>>>>
>>>> please try:
>>>>
>>>> postgres=# create or replace function foo()
>>>> returns void as $$
>>>> declare r x;
>>>> begin
>>>> for r in select * from x
>>>> loop
>>>> insert into y values(r.*);
>>>> end loop;
>>>> end;
>>>> $$ language plpgsql;
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>> 2012/4/18 thomas veymont <thomas(dot)veymont(at)gmail(dot)com>:
>>>>> (sorry my previous email was truncated)
>>>>>
>>>>> hi,
>>>>>
>>>>> Here is what I want to do :
>>>>>
>>>>> I want to check each row of a table against some conditions (this
>>>>> check needs some
>>>>> processing stuff I can easily code with pl/pgsql).
>>>>>
>>>>> If the row is OK, I want to add it in a "resulting table",
>>>>> else I just ignore the current row and go to next one.
>>>>>
>>>>> My function looks like this : (simplified)
>>>>>
>>>>> FUNCTION myfunction (...) RETURNS TABLE ( elem1 , elem2, elem3 ...)
>>>>> DECLARE
>>>>> g RECORD
>>>>> BEGIN
>>>>> FOR g in SELECT colum1, column2, ... FROM someTable
>>>>> LOOP
>>>>> -- do some processing on "g", then decide wheter I want to
>>>>> select it or not
>>>>> IF (g is selected) THEN >>add g to resulting_table<<
>>>>> END LOOP
>>>>> RETURN resulting_table
>>>>>
>>>>> How should I write the "add g to resulting table" part ?
>>>>>
>>>>> thanks,
>>>>> Tom
>>>>>
>>>>> --
>>>>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-sql
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2012-04-19 18:32:15 | Re: Uniform UPDATE queries |
Previous Message | thomas veymont | 2012-04-19 11:56:16 | Re: plpgsql : adding record variable to table |