18

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sunder
  • 1,445
  • 2
  • 12
  • 22

2 Answers2

21

Untested but should be about right:

CREATE OR REPLACE FUNCTION getcustomers() RETURNS SETOF customers AS $$
SELECT * FROM customers;
$$ LANGUAGE sql;
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 3
    this works, thank you. But pg seems to return just one column with comma sep'd values instead of actual columns, what gives? – Sunder Jan 06 '13 at 15:58
  • 4
    @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 07 '13 at 01:20
19

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228