Issue when returning result in PostgreSQL function

Question

I come from SQL Server world to PostgreSQL (9.0), and I am having a issue while migrating an stored procedure / function. The function is returning this error message:

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

I need to return both, the query results and the two out params. The query results are represented as two columns named 'paramName' and 'value' with one row of data per select query.

What I need to do pass the values from columns selected by the query along with the OUT parameters in different resultsets like Transact-SQL does perfectly and avoid getting that error message?

This is the pl/pgsql function:

        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 SETOF RECORD AS
        $BODY$
        DECLARE 
        val1 varchar;
        val2 varchar;
        val4 varchar;
       BEGIN
        -- A couple of IF THEN ommited here
        IF (v_solution_id IS NULL) THEN
            val1 := (Select 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;
        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 'mycolumnname3' as paramName,mycolumn3 as value  FROM tb2 
            WHERE column1 = val1 AND
            column4 = val4;      

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

            EXCEPTION WHEN OTHERS THEN 
                o_call_message := SQLERRM;
                o_call_status := SQLSTATE;
        end;

I am getting just the query result or the out params in the resultset. I am not able to figure out how to have both in the same function response.

UPDATE: Implemented with cursors as suggested by Erwin:

       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 refcursor AS
        $BODY$
        DECLARE 
        val1 varchar;
        val2 varchar;
        query_cursor refcursor;
       BEGIN
        -- A couple of IF THEN ommited here
        IF (v_solution_id IS NULL) THEN
            val1 := (Select 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;
        END IF;

            open  query_cursor for 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 'mycolumnname3' as paramName,mycolumn3 as value  FROM tb2 
            WHERE column1 = val1 AND
            column4 = val4;     

            o_call_status := 0;
            o_call_message := '';
            RETURN query_cursor;

            EXCEPTION WHEN OTHERS THEN 
                o_call_message := SQLERRM;
                o_call_status := SQLSTATE;
       end;

select * from  myplfunction(param1,param2,param3,param4);

But I get an error:

ERROR: function result type must be record because of OUT parameters
SQL state: 42P13

So this means I can not return a cursor when I have OUT params?

Also, does the return; in the IF THEN clause sentence terminates the function as intended?

Answer 1

Your code has a number of mistakes. This should work:

CREATE OR REPLACE FUNCTION myplfunction(i_param1 text, i_param2 text
                                      , i_param3 text, i_param4 text)
  RETURNS TABLE(param_name text, param_value text) AS
$func$
DECLARE 
   val1 text;
   val2 text;
   o_call_status integer;   o_call_message text;   -- without purpose
BEGIN
   IF v_solution_id IS NULL THEN
      val1 := (SELECT 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';
         -- I suggest this alternative:
         RAISE EXCEPTION 'column1 is not configured or invalid';
      END IF;
   END IF;

   RETURN QUERY
   SELECT 'mycolumnname1'::text, mycolumn1 FROM tb1 WHERE column1 = val -- val?
   UNION ALL
   SELECT 'mycolumnname2', mycolumn2 FROM tb1 WHERE column1 = val       -- val?
   UNION ALL
   SELECT 'mycolumnname3', mycolumn2 FROM tb2 WHERE column2 = val2 
                                              AND tb2paramName4 = i_val3;      
   o_call_status := 0;
   o_call_message := '';

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

• Solution to the main problem: Use RETURN QUERY to actually return the result. The SELECT without target caused the error message.

• Multiple sloppy mistakes: missing single quote, supernumerary commas, missing END IF;, multiple WHERE clauses ...

• You need to cast the string literal to match the declared output type: 'mycolumnname1'::text

• The form val1 := column1 FROM tbl2 WHERE ...; is possible but discouraged. It's better not to mix plpgsql and SQL code this way. Use the alternative I provided (only for a single parameter) or SELECT INTO.

• I would advise not to use CaMeL-cased identifiers, even if that's allowed. Unless double-quoted those are converted to lower case.