Re: Creating Stored Procedures

> I installed 7.3beta3 but how do I do it now?
> How do I make a function that returns a dataset
>
> Greets
> Zertox

He is an email I received from Joe Conway on the subject a month or so ago

> Adam Witney wrote:
>> There have been a few emails recently concerning using functions. However I
>> am a little confused as to their use with composite types. I can see how to
>> return a whole row from a table, but is it possible to return multiple
>> fields that do not originate from the same table?
>
> Sure. But you either need a named composite type that matches the row you want
> to return, or you can use a record datatype and specify the column definitions
> in the sql statement at run time.
>
> A composite type exists for each table and view in your database, as well as
> any stand-alone composite types you define. So, for example:
>
> test=# create table foo (f1 int,f2 text);
> CREATE TABLE
> test=# create table bar (f3 int,f4 text);
> CREATE TABLE
> test=# create view foobar as select f1,f2,f4 from foo, bar where f1=f3;
> CREATE VIEW
> test=# insert into foo values(1,'a');
> INSERT 1105496 1
> test=# insert into foo values(2,'b');
> INSERT 1105497 1
> test=# insert into bar values(1,'c');
> INSERT 1105498 1
> test=# insert into bar values(2,'d');
> INSERT 1105499 1
>
> -- This uses a named composite type based on the view
> test=# create function getfoobar1() returns setof foobar as 'select f1,f2,f4
> from foo, bar where f1=f3' language sql;
> CREATE FUNCTION
> test=# select * from getfoobar1();
> f1 | f2 | f4
> ----+----+----
> 1 | a | c
> 2 | b | d
> (2 rows)
>
> -- This uses an anonymous composite type specified at runtime
> test=# create function getfoobar2() returns setof record as 'select f1,f2,f4
> from foo, bar where f1=f3' language sql;
> CREATE FUNCTION
> test=# select * from getfoobar2() as (f1 int,f2 text,f4 text);
> f1 | f2 | f4
> ----+----+----
> 1 | a | c
> 2 | b | d
> (2 rows)
>
> HTH,
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.