Function with SQL Query Has No Destination for Result Data

PostgreSQL: Query has no destination for result data

The stored procedure won't just return the result of the last SELECT. You need to actually return the value:

CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$
BEGIN
--- ....
RETURN(SELECT dblink_disconnect());
END
$$ LANGUAGE plpgsql;

You're getting the error because Postgres expects the function to return something of type text, but your function doesn't return anything.

Query has no destination for result data in function PostgreSQL

A non void function expects a RETURN corresponding to the data type (or structure) declared at RETURNS. In your case a table containing an integer and two character varying. Take a look at the following test function that returns a table with an int and a text column:

CREATE OR REPLACE FUNCTION myfunc(int)
RETURNS TABLE(val int, txt text) LANGUAGE 'plpgsql'
AS $$
BEGIN
CREATE TEMPORARY TABLE tmp(id int,res text) ON COMMIT DROP;

IF $1=0 THEN
INSERT INTO tmp VALUES ($1,'Invalid');
ELSE
FOR i IN 1..$1 LOOP
INSERT INTO tmp VALUES (i,'txt '||i);
END LOOP;
END IF;

RETURN QUERY SELECT id,res FROM tmp;
END;
$$;

Test

SELECT * FROM myfunc(2);
val | txt
-----+-------
1 | txt 1
2 | txt 2
(2 rows)

Demo: db<>fiddle

A few thoughts:

  • In plpgsql it is possible to have multiple RETURN statements, so that different parts of your code return something different without exiting the function. But in case you prefer to have an intermediate table to collect the information and return it only once in the end, make sure the table is either manually dropped after the function is completed (or if it raises an error!) or simply create it as ON COMMIT DROP. If you prefer the former, take a look at UNLOGGED TABLES.
  • Consider using text over character varying.
  • Your WHILE loop uses an incremental value of 1 until it reaches a certain limit. You could use a FOR loop and get rid of the variable i in the DECLARE clause. It's no big deal but depending on your function size it might make things cleaner.
  • You can use the parameter order instead of its name, e.g. $1. It is also no big deal but it avoids conflicts and/or confusion with columns that might have the same name.

Function with SQL query has no destination for result data

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;

query has no destination for result data error even after having return in the function

There are more than one issues:

  1. Result of unbind queries is not result of function in Postgres. You need to use INTO clause.

  2. regexp_split_to_array is scalar function, there is not any reason to call this function from SELECT statement. Use SELECT only when you take result of table function, or when you need to read data from relations.

  3. assign statement in plpgsql is based on := symbol. The command SET is used for something different.

  4. the type text is proffered against varchar for function's parameters.

So your code can looks like:

CREATE OR REPLACE FUNCTION test(ulds text)
RETURNS boolean AS $$
DECLARE
result boolean;
target text[];
BEGIN
-- suboptimal, don't do this!!!
SELECT regexp_split_to_array('BLK&AAK&AKE', '&') INTO target;

-- preferred
target := regexp_split_to_array('BLK&AAK&AKE', '&');

result := true;
RETURN result;
END;
$$ LANGUAGE plpgsql;

Postgres database Query has no destination for result data error

You do not need the do statement for this, just plain SQL:

SELECT * FROM MSG
WHERE
NOT EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))
OR msg_timestamp >= (SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED');

Or to be more verbose:

SELECT * FROM MSG
WHERE
CASE
WHEN EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))
THEN msg_timestamp >= (SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED')
ELSE TRUE
END;

Or to be more simple:

SELECT * FROM MSG
WHERE
msg_timestamp >= coalesce(
(SELECT start_time FROM PROCESS WHERE NAME = 'TRANSACTION' AND STATUS = 'STARTED'),
'-infinity');

BTW If I understand correctly

EXISTS (SELECT ID FROM PROCESS WHERE ID = (SELECT MAX(ID) FROM PROCESS WHERE NAME = 'TRANSACTION'))

could be simplified to

EXISTS (SELECT 1 FROM PROCESS WHERE NAME = 'TRANSACTION')

query has no destination for result data in a function that has a set of instructions in postgresql

query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead.

You are getting this error because you do not assign the results to any variable in the function. In a function, you would typically do something like this instead:

select * into var1 from MY_TABLE;

Therefore, your function would look something like this:

CREATE OR REPLACE FUNCTION createTable() RETURNS int AS $$
DECLARE
var1 my_table%ROWTYPE;
BEGIN
DROP TABLE IF EXISTS MY_TABLE;
CREATE TABLE MY_TABLE
(
ID integer
)
WITH (
OIDS=FALSE
);

insert into MY_TABLE values(1);

select * into var1 from MY_TABLE;
<do something with var1>
RETURN 'SUCCESS';
END;
$$ LANGUAGE plpgsql;

Otherwise, if you don't put the results into a variable, then you're likely hoping to achieve some side effect (like advancing a sequence or firing a trigger somehow). In that case, plpgsql expects you to use PERFORM instead of SELECT

Also, BTW your function RETURNS int but at the bottom of your definition you RETURN 'SUCCESS'. SUCCESS is a text type, not an int, so you will eventually get this error once you get past that first error message -- be sure to change it as necessary.



Related Topics



Leave a reply



Submit