From: Richard Huxton <dev@archonet.com>
To: Klay Martens <kmartens@wol.co.za>
Cc: pgsql-sql@postgresql.org
Subject: Re:
Date: Thu, 09 Mar 2006 13:59:16 +0000
Message-ID: <441034B4.1060400@archonet.com> (raw)
In-Reply-To: <b62baae3da9741128a55d5039b7845ea@wol.co.za>
References: <b62baae3da9741128a55d5039b7845ea@wol.co.za>
Klay Martens wrote:
> Hi all.
>
> I am new to postgres, so I am still learning the basics.
>
> In Sequel Server, one can set up a function to return a table eg:
>
>
>
> CREATE FUNCTION [dbo].[AuthCodes] (@CLIENTID INT)
> RETURNS @AuthCodes TABLE
[snip]
> and then use select into or insert to populate the table.
>
> I am really battling to figure out how to do the same in a postgres function.
>
> It seems like one would have to create a user defined type (myType for eg), and
> then set the return type of the function to be a set of myType, but I can’t seem
> to make it work. I am guessing that I am on the wrong track…does anyone have any
> suggestions, or examples I could follow?
You've got the right idea. If there's not an existing table, define a
type, return SETOF my_type and use RETURN NEXT inside the function to
return each row. You call the function as: "SELECT * FROM myfunc()",
that is - treat it as a table.
There's an item on set-returning functions here:
http://techdocs.postgresql.org/
and here
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, kmartens@wol.co.za
Subject: Re:
In-Reply-To: <441034B4.1060400@archonet.com>
* 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