Re: Stored Procedure Question

On Sun, Jun 18, 2006 at 07:20:59PM +0200, Greg Quinn wrote:
> I am using 8.1. Now I am trying to create my function that returns multiple
> columns. I have added an OUT parameter, but of what type must it be?

Declare an OUT parameter for each column with that column's type.
If you want to return exactly one row then omit RETURNS; if you
want to return zero or more rows then return SETOF record.

> Any type I try to return it tells me that only one column can be returned.
>
> Here is my function...
>
> CREATE OR REPLACE FUNCTION "public"."sp_getcontacts" (out myrecord varchar)
> RETURNS varchar AS
> $body$
> select firstname, lastname from contacts
> $body$
> LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

I think you're after something like this:

CREATE OR REPLACE FUNCTION sp_getcontacts(OUT firstname varchar,
                                          OUT lastname varchar)
RETURNS SETOF record AS $$
  SELECT firstname, lastname FROM contacts
$$ LANGUAGE sql STABLE;

You'd call this function like so:

SELECT * FROM sp_getcontacts();

Is that what you're looking for?

--
Michael Fuhr