Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and have it return that resultset without having to specify the format of the output and change that definition every time I update the query. Is this even possible in PostgreSQL ?

I've tried the following using PostgreSQL 9.2:

CREATE OR REPLACE FUNCTION test() RETURNS SETOF record

Which gives me the following error:

ERROR: a column definition list is required for functions returning "record"

I've also tried:

CREATE OR REPLACE FUNCTION test() RETURNS table ()

but apparently that's invalid syntax.

share|improve this question
1  
@RolandoMySQLDBA: Just to clarify: Postgres is an "officially" accepted abbreviation for PostgreSQL. Consider the second link in the info page. Personally I do prefer PostgreSQL in the title ... – Erwin Brandstetter Apr 30 at 21:48

1 Answer

up vote 1 down vote

This is a misunderstanding. A function like this is perfectly valid:

CREATE OR REPLACE FUNCTION test()
 RETURNS SETOF record AS
$func$
VALUES (1, 2), (3, 4);  -- 2 rows with 2 integer columns
$func$ LANGUAGE sql;

However, since it returns anonymous records, you are required to provide a column definition list with every call , just like the error message told you. SQL demands to know what to expect from a function up front. The call would work like this:

SELECT * FROM test() AS f(a int, b int);

Functions returning anonymous records have their uses. I try to avoid them mostly, because the call is rather unwieldy. Depending on your exact requirements, there are a couple of ways around it. I wrote a comprehensive answer for that on SO recently.

share|improve this answer
Hm, that's frustrating. I've never written a column definition list either in a stored procedure definition or call in either MSSQL or MySQL and it's always worked fine. – SWrobel Apr 30 at 23:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.