Postgres functions

jalexander3 · #1 (**permalink**) 03-23-11, 20:22

Hello,

I come to postgres from a SQL server background.
I am trying to write a stored procedure (function) which does a simple select and returns the record.

I am accustomed to writing it like this:

BEGIN

SELECT *
FROM myTable
WHERE id = 3

END

Postgres wants me to declare a return type, so I set that to RECORD. Then of course I need to assign the result from the select into a variable of type RECORD and then return it (ugh...)

So I do that and I get a single record of (column1,column2,column3,...) So am I expected to return this result and perform some magic comma-delimiter parsing?

That seems silly, so instead I return a TABLE. But I can't simply return a TABLE. But to do that I need to define that table in the function signature where the return type is declared. Ok, that is completely useless since I need it to mirror myTable.

So I change the return type to MYTABLE and amazingly that acts exactly the same as if I had used RECORD.

Am I missing something? Or does Postgres really make you jump through hoops to return a simple record?

shammat · #2 (**permalink**) 03-24-11, 02:59

[code]So I do that and I get a single record of (column1,column2,column3,...) So am I expected to return this result and perform some magic comma-delimiter parsing? [/quote]No you declare the function as returning a SETOF which is clearly documented in the manual.

There is even an example of a function that returns the result of a SELECT statement there:

http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-EXAMPLES

In your case it's a lot simpler, just use "RETURNS TABLE".

One very important rule when porting from System A to System B: do not assume things work the same. And accept that you need to think differently now.
Simply complaining "I can't believe SystemB does not work the same as SystemB" won't get you far

artacus72 · #3 (**permalink**) 03-25-11, 12:22

I agree. If you go into this trying to do everything the SQL Server way you'll just frustrate yourself.

Code:

CREATE OR REPLACE FUNCTION get_customer(INT default NULL) 
RETURNS SETOF customer AS 
$$
  SELECT * FROM customer WHERE customer_id = COALESCE($1, customer_id);
$$
LANGUAGE SQL;

Now as you noticed, if you just call the function in the column list you'll get comma separated text back. But it really is a record type. To access it as such try one of these approaches.

Code:

SELECT (get_customer()).*;

SELECT * FROM get_customer(100);

SELECT c.customer_id, c.store_id, c.first_name 
FROM get_customer(100) c;

Internet Services

Web Development

Digital Marketing

Consumer Tech