Обсуждение: Help making a plpgsql function?
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... Can someone get me started? I have not written many function in plpgsql before, so any help is appreciated... (how to a create the return "record", etc?) btw, I am using PostgreSQL 7.4 (will be upgrading to 8.1 after the summer but need a function working for 7.4 first...) Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
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.
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 <dany@alchimerys.be> 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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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
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 <desoi@pgedit.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 >
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@pgedit.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 >
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
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 <dany@alchimerys.be> 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 majordomo@postgresql.org 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. # #================================================== JanWieck@Yahoo.com #