up vote 6 down vote favorite

How do I convert a simple select query like select * from customers into a stored procedure / function in pg?

I'm new to Postgres and create function customers() as returns table/setof just didn't feel right and thus the question here.

I understand procs are called "functions" in pg land. Thus create procedure does not exist and my only options are to either create a view or a function. The issue is create function x() returns setof y returns a paren'd comma separated row of values which can't be used without further processing (at least that's what I'm seeing in pgAdmin and Ruby/Sequel).

create function x() returns table(...) requires I embed the row definition which I don't want to.

I'm sure there's a reason behind all this but I'm surprised that the most common use case is this tricky.

share|improve this question
3  
What have you tried? – user554546 Jan 6 '13 at 1:56
2  
@rebnoob Rather than "didn't work" - specify the full text of the function you tried and the resulting error message as well as your PostgreSQL version. – Craig Ringer Jan 6 '13 at 4:01
    
Great! Thank you Erwin and Craig. – rebnoob Jan 7 '13 at 3:04
up vote 11 down vote

Untested but should be about right:

CREATE OR REPLACE FUNCTION getcustomers() RETURNS SETOF customers AS $$
SELECT * FROM customers;
$$ LANGUAGE sql;
share|improve this answer
1  
this works, thank you. But pg seems to return just one column with comma sep'd values instead of actual columns, what gives? – rebnoob Jan 6 '13 at 15:58
1  
@rebnoob You're calling it with something like SELECT x FROM getcustomers() x. You want to use SELECT getcustomers(); or SELECT * FROM getcustomers();. – Craig Ringer Jan 7 '13 at 1:20
up vote 11 down vote

The issue is "create function x() returns setof y" returns a paren'd comma separated row values which can't be used without further processing

Since the function returns a record / row type, you have to call it with

SELECT * FROM getcustomers();

to decompose the returned row into individual columns.

The manual on CREATE FUNCTION should be a good starting point. The example section covers this topic.

share|improve this answer
    
You sir.. are a jedi and force to reckon with! – faizanjehangir Mar 7 '16 at 19:09

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.