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:
Show quoted text
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 plpgsqlexecuting 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:
Show quoted text
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
needcreate 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:
Show quoted text
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
needcreate 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 plpgsqlexecuting 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 #