Search Postgresql Archives

Re: Help making a plpgsql function?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux