From: Adam Witney <awitney@sghms.ac.uk>
To: Jonas Wouters <jonas@vanroey.be>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Creating Stored Procedures
Date: Wed, 30 Oct 2002 09:42:20 +0000
Message-ID: <B9E559FC.A64F%a.witney@sghms.ac.uk> (raw)
In-Reply-To: <A6FF006530424A42AA2924E5CCBE3529084389@msx.vanroey.be>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: awitney@sghms.ac.uk, jonas@vanroey.be
Subject: Re: Creating Stored Procedures
In-Reply-To: <B9E559FC.A64F%a.witney@sghms.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox