Re: Help making a plpgsql function?

btw, just one more thing...

If I join two or more tables, which type should I use for the "into" variable (select into t2 *...) where
t2 would contain fields from more than one table?

I guess using "t2 some_table%rowtype" won't work when joining tables?

BTJ

On Wed, 5 Jul 2006 16:32:10 -0400
John DeSoi <desoi(at)pgedit(dot)com> wrote:

>
> 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
>