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 asON COMMIT DROP
. If you prefer the former, take a look atUNLOGGED TABLES
. - Consider using
text
overcharacter varying
. - Your
WHILE
loop uses an incremental value of 1 until it reaches a certain limit. You could use aFOR
loop and get rid of the variablei
in theDECLARE
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:
Result of unbind queries is not result of function in Postgres. You need to use
INTO clause
.regexp_split_to_array
is scalar function, there is not any reason to call this function fromSELECT
statement. UseSELECT
only when you take result of table function, or when you need to read data from relations.assign statement in plpgsql is based on
:=
symbol. The commandSET
is used for something different.the type
text
is proffered againstvarchar
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
SQL Script to Alter All Foreign Keys to Add on Delete Cascade
How to Create a New Database with the Hstore Extension Already Installed
To Ignore Duplicate Keys During 'Copy From' in Postgresql
How to Retrieve the Current Value of an Oracle Sequence Without Increment It
Database Naming Conventions by Microsoft
Use Variable with Top in Select Statement in SQL Server Without Making It Dynamic
How to Replace Specific Values in a Oracle Database Column
How to Use Alias in Where Clause
How to Compare Dates in SQL Server
How Do Null Values Affect Performance in a Database Search
Select Latest Row for Each Group from Oracle
Why Does the Wm_Concat Not Work Here
Oracle:Select Maximum Value from Different Columns of the Same Row