Thread: Help making a plpgsql function?

Help making a plpgsql function?

From

Bjørn T Johansen

Date:

05 July 2006, 14:47:08

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"
-----------------------------------------------------------------------------------------------

Re: Help making a plpgsql function?

From

Dany De Bontridder

Date:

05 July 2006, 17:46:18

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.

Re: Help making a plpgsql function?

From

Bjørn T Johansen

Date:

05 July 2006, 19:51:32

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

Re: Help making a plpgsql function?

From

John DeSoi

Date:

05 July 2006, 20:33:27

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

Re: Help making a plpgsql function?

From

Bjørn T Johansen

Date:

05 July 2006, 21:32:54

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
>

Re: Help making a plpgsql function?

From

Bjørn T Johansen

Date:

05 July 2006, 21:41:45

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
>

Re: Help making a plpgsql function?

From

John DeSoi

Date:

06 July 2006, 01:04:14

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

Re: Help making a plpgsql function?

From

Jan Wieck

Date:

11 July 2006, 21:23:09

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 #