Message-ID: <441034B4.1060400@archonet.com>
Date: Thu, 09 Mar 2006 13:59:16 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051201)
MIME-Version: 1.0
To: Klay Martens <kmartens@wol.co.za>
Cc: pgsql-sql@postgresql.org
Subject: Re: 
References: <b62baae3da9741128a55d5039b7845ea@wol.co.za>
In-Reply-To: <b62baae3da9741128a55d5039b7845ea@wol.co.za>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable

Klay Martens wrote:
> Hi all.
>=20
> I am new to postgres, so I am still learning the basics.
>=20
> In Sequel Server, one can set up a function to return a table eg:
>=20
> =20
>=20
> CREATE FUNCTION [dbo].[AuthCodes] (@CLIENTID INT)=20
> RETURNS @AuthCodes TABLE
[snip]
> and then use select into or insert to populate the table.
>=20
> I am really battling to figure out how to do the same in a postgres fun=
ction.
>=20
> It seems like one would have to create a user defined type (myType for =
eg), and=20
> then set the return type of the function to be a set of myType, but I c=
an=92t seem=20
> to make it work. I am guessing that I am on the wrong track=85does anyo=
ne have any=20
> suggestions, or examples I could follow?

You've got the right idea. If there's not an existing table, define a=20
type, return SETOF my_type and use RETURN NEXT inside the function to=20
return each row. You call the function as: "SELECT * FROM myfunc()",=20
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

--=20
   Richard Huxton
   Archonet Ltd