Query has no destination for result data, although it should

Question

Here is a simplified version of a function I have:

CREATE OR REPLACE FUNCTION my_funct(user_id integer)
  RETURNS integer AS
$BODY$
DECLARE
  my_var integer;

BEGIN
  //....................
  if not exists (select * from table1) then
    return 0;
  end if;

  my_var := (SELECT COUNT(*) FROM table2
    inner join ..............
  );

  RETURN (select field1 from table3) - my_var;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION my_funct(integer)
  OWNER TO postgres;

But I call from Play framework, I get the error:

[PSQLException: ERROR: query has no destination for result data 
Hint: If you want to discard the results of a SELECT, use PERFORM instead. 
Where: PL/pgSQL function my_funct(integer)   at SQL statement]

UPDATE:

 ------------------------------------------------

  if not exists (select * from table1) then
    select try_to_update_table1(user_id); -- probably the cause. only updates the table1
  end if;

 if not exists (select * from table1) then
    return 0;
  end if;

 ------------------------------------------------

Please post an unedited version that reproduces the problem in a runnable form. Seeing a cut-down version doesn't help a great deal. — Craig Ringer, May 17 '14 at 06:59
This is probably because you need to use into when you have select within a plpgsql function, ie, select something into somevalue. But, as Craig says, without seeing full version and line where the problem occurs, hard to help. — John Powell, May 17 '14 at 07:06

Patrick · Answer 1 · 2014-05-17T12:04:56.087

1

CREATE OR REPLACE FUNCTION my_funct(user_id integer) RETURNS integer AS
$BODY$
DECLARE
  my_var integer;
BEGIN
  //....................
  PERFORM * FROM table1;
  IF NOT FOUND THEN
    RETURN NULL;
  END IF;

  SELECT COUNT(*) INTO my_var
  FROM table2
  INNER JOIN
    ..............
  ;

  RETURN (SELECT field1 FROM table3) - my_var;
END;
$BODY$ LANGUAGE plpgsql VOLATILE STRICT;

You would do well to read the documentation on PL/pgSQL before you post your question.

Additionally, do not return 0 as an error code, return NULL or RAISE an exception instead. This is the standard behaviour in all database environments (what if field_1 FROM table3 has the same value as my_var?)

edited May 17 '14 at 12:04

answered May 17 '14 at 10:16

Patrick

29,357
6
62
90

It probably also needs `RETURN QUERY select field1 - my_var from table3;`. (And possibly with `limit 1` to ensure it's not returning multiple rows.) – Denis de Bernardy May 17 '14 at 10:18
`column "exists" does not exist` – Incerteza May 17 '14 at 11:03
@Alex: My bad. Instead of `EXISTS` it should read `FOUND`. Answer updated. The scourge of cut&paste. `EXISTS` tests for the presence of data in a sub-query; `FOUND` is an internal PL/pgSQL parameter that is set when a preceding SQL statement returns data (`SELECT` obviously, but same effect with `INSERT` and `UPDATE`). – Patrick May 17 '14 at 12:04
This does not answer the question (which is actually broken). The suggested changes do *not* improve the original. The only actual problem - `(select field1 from table3)` without `WHERE` condition is not addressed (but is also just an aside). – Erwin Brandstetter May 17 '14 at 22:57
If `table3` holds a single row then there is no problem. That may not be a very common situation, but it does occur. As an example, an application may store run-time parameters in a table with columns for each of the parameters and a single row for values; the final statement could be just that (e.g. how different is `my_var` from an initial value?). The `information_schema_catalog_name` table is another example of a table which is declared to have exactly one row. – Patrick May 18 '14 at 02:16
Possible, agreed. Suspicious, though. – Erwin Brandstetter May 18 '14 at 02:30
please take a look at my updated question as I think I found the clue. – Incerteza May 18 '14 at 05:42

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

The code you display should just work as is. (I tested in 9.3 and it does.)
The error message is unrelated and caused by something not in your question. An answer for that:
Error: query has no destination for result data while using a cursor

Asides

(select field1 from table3) without WHERE condition doesn't make a lot of sense, but would still work.

Your original code:

IF NOT EXISTS (SELECT * FROM table1) THEN
   RETURN 0;
END IF;

is clearly superior to the suggested alternative:

PERFORM * FROM table1;
IF NOT FOUND THEN
   RETURN NULL;
END IF;

Returning 0 or NULL is a question of requirements. There is no "standard behavior".

edited Jun 20 '20 at 09:12

Community

1
1

answered May 17 '14 at 23:00

Erwin Brandstetter

605,456
145
1,078
1,228

Could you please elaborate why the original code is "clearly superior" than my suggested alternative? It is not clear to me. And I beg to differ on the issue of returning an error status that is in the same domain as a successful function completion. The phrase "standard behaviour" might not be accurate, but I challenge you to come up with an example in either the documentation or a built-in PL/pgSQL function that mixes error status and result domain in this way (i.e. where there is a potential collision between an error status and a valid function return code). – Patrick May 18 '14 at 02:06
@Patrick: (1) Superior because shorter, cleaner and faster. [Related question](http://stackoverflow.com/questions/12831667/how-to-check-if-a-row-exists-in-a-postgresql-stored-procedure/12833052) (2) For an error status I would agree with you. But who said anything about "error status"? For all I can tell, the function works as designed to return 0 in the first case. – Erwin Brandstetter May 18 '14 at 02:27
(1) When you say "shorter, cleaner", do you mean the code? In that case it is a matter of style rather than superiority. I tend to prefer more verbose coding because it is - in general - easier to maintain. And how much faster is `IF NOT EXISTS (SELECT ...)` relative to the `PERFORM/FOUND` pair? Considering that I am using the latter quite a lot that is an issue of interest. (2) Granted. But if so, my golly, what is the logic in the function? – Patrick May 18 '14 at 03:13
@Patrick: *Shorter* meaning length of code. I strongly prefer shorter code as long as it's still clear - and it's even clearer in this case. A *lot* *faster*, since `EXISTS` can stop evaluation at the first row, while `PERFORM * FROM table1` fetches the whole table. Add `LIMIT 1` to achieve comparable speed. Would still be slower, though, and even more verbose now. – Erwin Brandstetter May 18 '14 at 03:40
Ah yes, the optimization in `EXISTS`. But doesn't `PERFORM` have similar optimizations, considering that it does not return anything because there is nowhere to place the result? And seeing that the OP tests for the existence of any data in `table1`, I would assume that the table is used to stage data for further processing or something like that. But, yes, that is just an assumption. Would be good to see the whole function instead of an abbreviated version where the logic has been lost. – Patrick May 18 '14 at 04:09
please take a look at my updated question as I think I found the clue. – Incerteza May 18 '14 at 05:40
@Alex: Yes, the update shows an offending statement. Use `PERFORM` instead of `SELECT` and the syntax is correct. Your logic, however, does not look clean at all. It goes like "if there is no data in table1, then try_to_update_table1(), then return with a value of 0 if there is no data in table1". Looks crummy to me. Care to post more complete code as others have suggested already? – Patrick May 18 '14 at 06:39

Query has no destination for result data, although it should

2 Answers2

Asides