Re: [GENERAL] Help making a plpgsql function?
On 7/5/2006 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? I wonder why your problem can't be solved by a simple join. Jan BTJ On Wed, 5 Jul 2006 19:13:39 +0200 Dany De Bontridder <[EMAIL PROTECTED]> wrote: On Wednesday 05 July 2006 16:46, Bjørn T Johansen wrote: > I need to make a funtion that take one parameter and then returns a > "record" with x number of fields, collected from x no. of tables, i.e. I > need to run several sql statemtents to collect all the needed values from x > no. of fields and insert it into a "record" and return the "record" at the > end... From http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html Example for function having return type set of record create function testfunc(int) returns record as ' declare output record; begin for output in select * from table1 where col1<$1 loop return next output; end loop; return; end; ' language plpgsql executing through sql as: select * from testfunc(6) as (col1 int, col2 float, col3 char(20)); Regards, D. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help making a plpgsql function?
On Jul 5, 2006, at 5:41 PM, Bjørn T Johansen wrote: 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? I think you can declare t2 as type record (e.g. "t2 record;" in the declaration). Alternately, you can declare whatever variables you need to get from the join and select into them explicitly. Something like: select into v1, v2, v3 col1, col2, col3 from t1 join t2 John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 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 <[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
Re: [GENERAL] Help making a plpgsql function?
That looks like the features I am looking for... I'll check it out more tomorrow, thx... :) 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help making a plpgsql function?
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? BTJ On Wed, 5 Jul 2006 19:13:39 +0200 Dany De Bontridder <[EMAIL PROTECTED]> wrote: > On Wednesday 05 July 2006 16:46, Bjørn T Johansen wrote: > > I need to make a funtion that take one parameter and then returns a > > "record" with x number of fields, collected from x no. of tables, i.e. I > > need to run several sql statemtents to collect all the needed values from x > > no. of fields and insert it into a "record" and return the "record" at the > > end... > From http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html > > Example for function having return type set of record > > create function testfunc(int) returns record as ' > declare > output record; > begin > for output in select * from table1 where col1<$1 loop > return next output; > end loop; > return; > end; > ' language plpgsql > > executing through sql as: > > select * from testfunc(6) as (col1 int, col2 float, col3 char(20)); > > > Regards, > > D. > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help making a plpgsql function?
On Wednesday 05 July 2006 16:46, Bjørn T Johansen wrote: > I need to make a funtion that take one parameter and then returns a > "record" with x number of fields, collected from x no. of tables, i.e. I > need to run several sql statemtents to collect all the needed values from x > no. of fields and insert it into a "record" and return the "record" at the > end... >From http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html Example for function having return type set of record create function testfunc(int) returns record as ' declare output record; begin for output in select * from table1 where col1<$1 loop return next output; end loop; return; end; ' language plpgsql executing through sql as: select * from testfunc(6) as (col1 int, col2 float, col3 char(20)); Regards, D. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
