Skip site navigation (1) Skip section navigation (2)

Re: Stored Procedure Question

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Greg Quinn <greg(at)officium(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Stored Procedure Question
Date: 2006-06-17 18:27:56
Message-ID: 20060617182756.GA72177@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Andrej Ricnik-BayDate: 2006-06-18 07:15:32
Subject: Comments on that page?
Previous:From: Bruno Wolff IIIDate: 2006-06-17 18:19:33
Subject: Re: cannot access directory /pg_tblspc/*

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group