Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unanswered: Postgres functions

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    [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

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •