Postgresql stored procedure return table all columns

Question

A function is created. The function has a input parameter. I can return a column but I want to return all table columns. Also I want to do if result is zero the function return just 0. How can I do it? Here the error result.

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function dwgcould.returnallcolumns(character varying) line 3 at SQL statement ********** Error ********** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function dwgcould.returnallcolumns(character varying) line 3 at SQL statement

CREATE OR REPLACE FUNCTION dwgcould.returnallcolumns(IN sessionId character varying)
  RETURNS SETOF public.mytable AS
$BODY$
BEGIN
    SELECT * FROM public.mytable WHERE session_id=returnallcolumns.sessionId ORDER BY pro_id DESC LIMIT 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

What is the error you get? But "I want to return all table columns" contradicts: "* the function return just 0*" you can't return "all columns" in one case and just one column in other cases. — a_horse_with_no_name
İf no result returning value will be 0. İt is not possible? — Fatih

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2017-03-03T13:03:09

If you want to return a result, you need to use return query in PL/pgSQL as documented in the manual

CREATE OR REPLACE FUNCTION dwgcould.returnallcolumns(IN sessionId character varying)
  RETURNS SETOF public.mytable AS
$BODY$
BEGIN
  return query --<< this was missing
    SELECT * 
    FROM public.mytable 
    WHERE session_id = returnallcolumns.sessionId 
    ORDER BY pro_id DESC LIMIT 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

But you don't need PL/pgSQL for this, a simple SQL function will be more efficient:

CREATE OR REPLACE FUNCTION dwgcould.returnallcolumns(IN sessionId character varying)
  RETURNS SETOF public.mytable AS
$BODY$
    SELECT * 
    FROM public.mytable 
    WHERE session_id = returnallcolumns.sessionId 
    ORDER BY pro_id DESC LIMIT 1;
$BODY$
LANGUAGE sql;

Postgresql stored procedure return table all columns

1 Answers