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 <[EMAIL PROTECTED]> 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 > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
