Simple PostgreSQL function to return rows

Question

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.

@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
– Craig Ringer, Commented Jan 6, 2013 at 4:01

Craig Ringer · Accepted Answer · 2013-01-06 04:02:34Z

23

Untested but should be about right:

CREATE OR REPLACE FUNCTION getcustomers() RETURNS SETOF customers AS $$
SELECT * FROM customers;
$$ LANGUAGE sql;

answered Jan 6, 2013 at 4:02

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sunder Over a year ago

this works, thank you. But pg seems to return just one column with comma sep'd values instead of actual columns, what gives?

Craig Ringer Over a year ago

@rebnoob You're calling it with something like SELECT x FROM getcustomers() x. You want to use SELECT getcustomers(); or SELECT * FROM getcustomers();.

Erwin Brandstetter · Accepted Answer · 2020-07-23 21:20:37Z

22

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

The function returns a row. To decompose into individual columns, call it with:

SELECT * FROM getcustomers();

That's assuming the function defines a proper return type. See:

How to return multiple rows from PL/pgSQL function?

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

edited Jul 23, 2020 at 21:20

answered Jan 6, 2013 at 18:35

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

4 Comments

faizanjehangir Over a year ago

You sir.. are a jedi and force to reckon with!

balron Over a year ago

Wasted 1 day to understand what was the problem. I was calling function without " * from" part and EF Core was giving error about db model mapping. Because data was comma, separated. Thanks :)

TheRealChx101 Over a year ago

I get this a column definition list is required for functions returning "record"

Erwin Brandstetter Over a year ago

@TheRealChx101: See: stackoverflow.com/a/17281457/939860

Collectives™ on Stack Overflow

Simple PostgreSQL function to return rows

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related