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?

Please see the examples in the manual: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING and http://www.postgresql.org/docs/current/static/sql-createfunction.html#SQL-CREATEFUNCTION-EXAMPLES — , Jun 06 '13 at 14:32
I need it to be plpgsql function. Plus with language being SQL, i would need to put the query statement into string with quotations and I don't want to do that. — Horse Voice, Jun 06 '13 at 14:46
@ImtiazAhmad: with `language sql` you can use the same `$body$` quoting as with `plpgsql`. The quoting style has nothing to do with the language. — , Jun 06 '13 at 14:52
When I use the `language sql`, it doesnt compile the function. I get an error. `ERROR: syntax error at or near "select" LINE 5: select a.date_value, a.date_id, a.date_desc from dates_t...` ^ — Horse Voice, Jun 06 '13 at 15:23
@HorseVoice: if you remove the BEGIN and END, the function will compile if the language is SQL. — Asegid Debebe, Nov 15 '14 at 03:44

score 57 · Accepted Answer · edited Jul 21 '17 at 12:26

57

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;

edited Jul 21 '17 at 12:26

wonea

4,783
17
86
139

answered Jun 06 '13 at 15:26

Clodoaldo Neto

118,695
26
233
260

Thank you for your response. I need to establish a dblink within the function too. Before executing the return query resultset. So here is something I would like to add before the return query clause and when I do add it, I get a similar error as above mentioned. `SELECT dblink_connect('remote_db')` How can I include that in the above plpgsql function? Thank you in advance – Horse Voice Jun 06 '13 at 15:49
@Clodoaldo Neto, +1 great!. – Sarfaraz Makandar May 30 '14 at 04:45
Working nicely, thanks. But how do I drop the '()' around the results? (apart from cutting them away using unixs cut) – gkephorus Jul 01 '14 at 09:08
Might need and additional `RETURN;` in [PostgreSQL 12](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING) – Rafs Jul 31 '20 at 16:09

score 3 · Answer 2 · edited Aug 11 '22 at 18:29

3

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;

edited Aug 11 '22 at 18:29

Akaisteph7

5,034
2
20
43

answered Oct 03 '19 at 07:45

Dekeli

103
6

3

when you answer it is recommended to specify some description to your answer. – Daniel Oct 03 '19 at 08:01
Particularly since the other answer already posted some code that apparently works. What does your answer offer that the other one doesn't? – Sneftel Oct 03 '19 at 08:13
My answer address the use in LANGUAGE PLPGSQL; - as was in the original questions The first answer suggested using LANGUAGE SQL instead of plpgsql When need other functionality of PlPgsql - this is the syntax that works Am I missing something? – Dekeli Oct 06 '19 at 07:47
1

The first answer suggests both. I think separating your description from the actual code clarifies some of what you meant to add. Which is still already in the accepted answer, albeit with slightly different syntax. – Akaisteph7 Aug 11 '22 at 18:31

score 0 · Answer 3 · answered Aug 11 '22 at 18:28

I couldn't do it as plain SQL as I needed to enter some data into a database for further processing and wanted to create a variable. Or at least I did not figure out the correct syntax for that. And the accepted answer had code I did not need, such as connecting to the database, as I ran this from inside pgAdmin with a connection setup already. I also had to drop the function when I made edits to it.

I was using this for inserting a geometry for intersection. A different use case and example could help someone else. This also shows how to then view this data and use it just like a table.

-- Get a geojson shape inside of postgres for further use
DROP FUNCTION fun();
CREATE OR REPLACE FUNCTION fun()
    RETURNS TABLE (geometry geometry) AS
$BODY$   
DECLARE geojson TEXT;
BEGIN
    geojson := '{
        "type":"Polygon",
        "coordinates":[[[-90.9516399548092,39.8942337977775],[-90.9513913202472,39.8936939306154],[-90.9522805177147,39.8937108246505],[-90.9549542293894,39.8937616571416],[-90.954948768846,39.8945506794343],[-90.9531755591848,39.894492766522],[-90.9531770788457,39.8942868819087],[-90.9516399548092,39.8942337977775]]],
        "crs":{"type":"name","properties":{"name":"EPSG:4326"}},
        }';
    return query (SELECT ST_GeomFromGeoJSON(geojson) AS geometry);
END;
$BODY$
      LANGUAGE plpgsql;

-- View test insert
SELECT * FROM fun()

Function with SQL query has no destination for result data

3 Answers3

Linked

Related