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:
WHERE id = 3
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?
[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:
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