Re: Help making a plpgsql function?

On Jul 5, 2006, at 3:51 PM, Bjørn T Johansen wrote:

> Yes, but I need to return n fields from one table and n fiels from
> another, and n fields from yet another
> table, etc... and return this as some kind of record... How do I to
> this?

Create a type. Something like

create type my_type as (i1 integer, t1 text); -- whatever fields you
need

create or replace function test ()
returns my_type as $$
declare
    mt my_type%rowtype;
    icol integer;
    t2 some_table2%rowtype;
begin
    select into icol integer_col from some_table1 where some_col =
some_val;
    select into t2 * from some_table2 where some_col = some_val;
    mt.i1 := icol;
    mt.t1 := t2.text_col;
    return mt;
end;
$$ language plpgsql;


And you can return multiple my_type records (a set returning
function) by changing the return type to "setof my_type" and then
returning multiple records from your function.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL