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 10:08:18 |
Message-ID: | CAFj8pRDPcAi5h9VZdZx82noXgf6ehbG=-bXFDcX1vQeJz8-5gg@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>:
> 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 | Dennis | 2012-04-19 10:55:41 | Re: Uniform UPDATE queries |
Previous Message | thomas veymont | 2012-04-19 09:43:05 | Re: plpgsql : adding record variable to table |