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. — user330315
– user330315, Commented Mar 3, 2017 at 12:45
İf no result returning value will be 0. İt is not possible? — Fatih
– Fatih, Commented Mar 3, 2017 at 12:48

user330315 · Accepted Answer · 2017-03-03 13:03:09Z

23

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;

answered Mar 3, 2017 at 13:03

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Fatih Over a year ago

Thank you so much.

balron Over a year ago

This return a single column(that name is "mytable"). In that column all values added with comma. How to get result with all columns like tables?

user330315 Over a year ago

@balron: did you use select * from returnallcolumns(...)?

balron Over a year ago

@a_horse_with_no_name No , finally i noticed that i need to query as select * from ... , thanks :)

Prashant Girase Over a year ago

How to get all data from a dynamic table which name pass as in parameter?

Prashant Girase · Accepted Answer · 2022-12-30 10:49:55Z

0

How to get all data from dynamic table in the PostgreSQL database which name pass as in parameter?

DROP FUNCTION IF EXISTS schemaName."GetAllDataFromDynamicTable";

CREATE OR REPLACE FUNCTION schemaName."GetAllDataFromDynamicTable"(IN P_DynamicTableName text)
    RETURNS SETOF schemaName."P_DynamicTableName" 
AS $$
BEGIN
  return query
    SELECT *
    FROM schemaName."P_DynamicTableName" -- this table name every time change
END;
$$
LANGUAGE plpgsql;

answered Dec 30, 2022 at 10:49

Prashant Girase

1371 gold badge1 silver badge11 bronze badges

Collectives™ on Stack Overflow

Postgresql stored procedure return table all columns

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related