What is a flexible way to create a function returning multiple rows? Im in development of a pg9.4 database, and it is not uncommon to modify the number of columns in the SELECT statements in my functions. However, as Ive learned plpgsql the RETURNS is specified as SETOF . Which is fine as long as the SELECT is a * or specifies all the table's columns in the query.
Is there a way to tell the RETURNS to use a SETOF the columns in the SELECT statement, or something along those lines? I saw that I could use RETURNS TABLE return multiple select , but I was hoping for something more dynamic. Thanks
CREATE TABLE business
(
retailerid integer NOT NULL,
retailername character varying(250) NOT NULL,
CONSTRAINT pkretailerid PRIMARY KEY (retailerid)
)
WITH ( OIDS=FALSE );
INSERT INTO business(retailerid, retailername)
VALUES(1,'Mc Donalds');
INSERT INTO business(retailerid, retailername)
VALUES(2,'Burger King');
CREATE OR REPLACE FUNCTION getretailers()
RETURNS SETOF business AS
$BODY$
BEGIN
RETURN QUERY
SELECT retailerid, retailername FROM business;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
SELECT getretailers(); -- works
drop function getretailers();
CREATE OR REPLACE FUNCTION getretailers()
RETURNS SETOF business AS
$BODY$
BEGIN
RETURN QUERY
SELECT retailername FROM business;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
SELECT getretailers(); -- err, structure of query does not match return type
