Date: Wed, 30 Oct 2002 09:42:20 +0000
From: Adam Witney <awitney@sghms.ac.uk>
In-reply-to: <A6FF006530424A42AA2924E5CCBE3529084389@msx.vanroey.be>
To: Jonas Wouters <jonas@vanroey.be>,
	pgsql-sql <pgsql-sql@postgresql.org>
Message-id: <B9E559FC.A64F%a.witney@sghms.ac.uk>
MIME-version: 1.0
User-Agent: Microsoft-Entourage/10.1.0.2006
Subject: Re: Creating Stored Procedures
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT

> 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@postgresql.org)
> 


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