Issue when migrating Stored Procedure from SQL Server to PostgreSQL

Question

I come from SQL Server to PostgreSQL (9.0), so I am having a issue in a stored procedure / function when executing it. The function is returning this error message:

SQLSTATE: 42601;
SQLERRM: query has no destination for result data

What I need to do pass the values from columns selected by the SELECT query along with the OUT parameters into the result and avoid getting that error message?

CREATE OR REPLACE FUNCTION myplfunction(
    IN i_param1 character varying,
    IN i_param2 character varying,
    IN i_param3 character varying,
    IN i_param4 character varying,
    OUT o_call_status integer,
    OUT o_call_message character varying)
  RETURNS record AS
$BODY$
DECLARE 
val1 varchar;
val2 varchar;
...

IF (v_solution_id IS NULL) THEN
    val1 := column1 FROM tbl2 WHERE column2= i_param1;
    IF(val1 IS NULL) THEN
        o_call_status := 1005;
        o_call_message := column1 is not configured or invalid';
        RETURN;
END IF;

    SELECT 'mycolumnname1' as paramName,mycolumn1 as value FROM tb1 WHERE column1 = val
    UNION ALL
    SELECT 'mycolumnname2' as paramName,,mycolumn2 as value  FROM tb1 WHERE column1 = val
    UNION ALL
    SELECT 'mycolumnname2' as paramName,,mycolumn2 as value  FROM tb2 WHERE column2 = val2 
    WHERE tb2paramName4=i_val3;      

    o_call_status := 0;
    o_call_message := '';

    EXCEPTION WHEN OTHERS THEN 
        o_call_message := SQLERRM;
        o_call_status := SQLSTATE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

If you want to return a result set you need to use `return query`. If you want to store the result of a `select` statement into a variable you need to use `select .. into variable from ...` — , Apr 16 '14 at 17:37
@a_horse_with_no_name that is helpul, but what about including values from the SELECT query and others variables that are not obtained in that query. Can I call "retun query" multiple times to add those values in the function resultset? or I need to do something else like calling `RETURN NEXT` each time I want to add a value into the resultset after getting it? — Douglas, Apr 16 '14 at 19:13
or do I need to create a record variable and then assign one by one... — Douglas, Apr 16 '14 at 19:48
In your case you'll need to loop over the query output using a `record` loop variable, then copy the record fields into your OUT parameters and `return next;`. — Craig Ringer, Apr 17 '14 at 00:04
You can also add the variable values to the SELECT - SELECT tb1paramName1,tb1paramName2,0 as c_status,'' as c_mesg FROM tb1. If the values are different for different records, this will not work. In case the variable value depends on the result of the SELECT, look at CASE construct. You can set the variable value in the SELECT using CASE. — Jayadevan, Apr 17 '14 at 02:38

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Generally, a PostgreSQL function only returns a single value (or row), or a set of values (or rows), but not both in parallel.

Return a table

You can either return the set or rows directly from the function, making it a "set-returning function", a.k.a. "table function". Then you have no other choice than to return the parameters o_call_status and o_call_message with every row (if you, in fact, need them at all?!)

CREATE OR REPLACE FUNCTION myplfunction(i_val1 int, i_val2 int)
  RETURNS TABLE (col1 text, col2 text          -- use matching types!
               , o_call_status  text           -- text, not int
               , o_call_message text) AS
$func$
BEGIN
   RETURN QUERY 
   SELECT col1, col2
        , '0'::text AS o_call_status
        , ''::text AS o_call_message   -- column aliases irrelevant here
   FROM   tb1
   WHERE  col3 = i_val1
   UNION ALL
   SELECT col4, col5, '0'::text, ''::text
   FROM   tb2
   WHERE  col6 = i_val2
   AND    col7 = i_val3;      -- WHERE was specified twice

   EXCEPTION WHEN OTHERS THEN 
     RETURN QUERY
     SELECT NULL::text, NULL::text, SQLERRM, SQLSTATE;
END
$func$  LANGUAGE plpgsql;

The special variable SQLERRM is of type text, btw. Not integer.

Or open a cursor

Or you would have to open a cursor to decouple the returned table from those two parameters.

CREATE OR REPLACE FUNCTION myplfunction(
      i_val1 text, i_val2 text, i_val3 text, i_val4 text
    , OUT o_call_status  text
    , OUT o_call_message text) AS
$func$
DECLARE
   curs CURSOR FOR
   SELECT col1, col2 FROM tb1
   WHERE  col3 = i_val1
   UNION ALL
   SELECT col4, col5 FROM tb2
   WHERE  col6 = i_val2
   AND    col7 = i_val3;
BEGIN
   OPEN curs;

   o_call_status  := '0';
   o_call_message := '';

   EXCEPTION WHEN OTHERS THEN 
     o_call_status  := SQLSTATE;
     o_call_message := SQLERRM;
END
$func$  LANGUAGE plpgsql;

However, the cursor only exists within the same transaction. So you need to fetch the values before you COMMIT or ROLLBACK.

BEGIN;
SELECT * FROM myplfunction (10, 20, 30, 40);
FETCH ALL IN curs;
ROLLBACK;

You can even hand in arbitrary cursor names. This and other details in the manual.

And there is more information about errors available since Postgres 9.3..

Issue when migrating Stored Procedure from SQL Server to PostgreSQL

1 Answers1

Return a table

Or open a cursor

Linked