returning a resultset from a function

Started by Anton Andreevabout 18 years ago2 messagesgeneral
Jump to latest
#1Anton Andreev
fn30762@fmi.uni-sofia.bg

Hi,

How do I return a result set? Is there a better way in 'plpgsql' than
the one described below?
I do not want to make a select from a function(which pretty useful in
many cases):
SELECT * FROM getfoo();
, but I want to just call the function with SELECT getfoo();

--DROP FUNCTION getfoo();
--DROP type compfoo;

CREATE TYPE compfoo AS (f1 integer,f2 integer);

CREATE OR REPLACE FUNCTION getfoo() RETURNS SETOF compfoo
AS
$BODY$
declare
ret_row record;
BEGIN

FOR ret_row IN SELECT id,mun_id FROM mytable LOOP

RETURN next ret_row;

END LOOP;

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

#2Erik Jones
erik@myemma.com
In reply to: Anton Andreev (#1)
Re: returning a resultset from a function

On Feb 15, 2008, at 2:56 PM, Anton Andreev wrote:

Hi,

How do I return a result set? Is there a better way in 'plpgsql'
than the one described below?
I do not want to make a select from a function(which pretty useful
in many cases):
SELECT * FROM getfoo();
, but I want to just call the function with SELECT getfoo();

This article covers a way to do that: http://www.postgresonline.com/
journal/index.php?/categories/9-advanced.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com