Function with SQL query has no destination for result data

Question

I am trying to create a function that returns a SELECTed resultset. When I call my postgres function like this select * from tst_dates_func() I get an error as shown below:

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 "tst_dates_func" 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 "tst_dates_func" line 3 at SQL statement 

Here is the function I created:

CREATE OR REPLACE FUNCTION tst_dates_func()      RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$    BEGIN     select a.date_value, a.date_id, a.date_desc from dates_tbl a; END; $BODY$       LANGUAGE plpgsql; 

I am not sure why I am getting the above error. I would like to run select * from tst_dates_func(); and get data back. Or further join the result set if needed. What is the problem here?

Answer 1

Do it as plain SQL

CREATE OR REPLACE FUNCTION tst_dates_func()      RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$        select a.date_value, a.date_id, a.date_desc from dates_tbl a;  $BODY$       LANGUAGE sql; 

If you really need plpgsql use return query

CREATE OR REPLACE FUNCTION tst_dates_func()      RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$    BEGIN     perform SELECT dblink_connect('remote_db');     return query     select a.date_value, a.date_id, a.date_desc from dates_tbl a;  END; $BODY$       LANGUAGE plpgsql; 

Answer 2

In PLPGSQL - use RETURN QUERY  CREATE OR REPLACE FUNCTION tst_dates_func()      RETURNS TABLE( date_value date, date_id int, date_desc varchar) as $BODY$    BEGIN     RETURN QUERY (select a.date_value, a.date_id, a.date_desc from dates_tbl a); END; $BODY$     LANGUAGE plpgsql;