Why am I getting the "Query has no destination for result" error?

Question

I'm starting on PL/pgSQL, and have written this code to update and return a query, but it isn´t working.

This is the SQL error:

SQL Error [42601]: ERROR: query has no destination for result data

Hint: If you want to discard the results of a SELECT, use PERFORM instead.

This is my code:

CREATE OR REPLACE FUNCTION setKeys(qty int,game_id varchar,nro_order varchar)
RETURNS TABLE (id int4, key varchar ) AS $$
DECLARE
    row_to_update record;
    contador int;
BEGIN
    contador := 0;
    LOOP
        -- Select the single row to update based on a condition
        SELECT sk.id 
        INTO row_to_update
        FROM serial_keys sk
        WHERE sk.id_game = game_id
        AND sk.state = 'Disponivel'
        AND sk.order_number IS NULL  
        LIMIT 1;
        IF row_to_update IS NULL THEN
            RAISE EXCEPTION 'No matching rows found.';
        END IF;
        select count(sks.id) as flag from serial_keys sks where sks.order_number = nro_order;
            if flag = 0 then
                UPDATE serial_keys
                SET order_number = nro_order,
                    state = 'enviado'
                WHERE id = row_to_update.id;
            end if;
        contador := contador + 1;   
    EXIT WHEN contador = qty; 
    END LOOP;
    RETURN QUERY SELECT s.id,s."key" FROM serial_keys s WHERE s.id = row_to_update.id; 
END;
$$ LANGUAGE plpgsql;

What am I doing wrong?

Answer 1

The error message is quite clear: in the PL/pgSQL context every SELECT command result must be stored somewhere (or entirely discarded, by replacing it with PERFORM). Since in your case you don't want to discard the result of

select count(sks.id) as flag from serial_keys...

you need to store it in a variable:

...
DECLARE
  flag int;
...
BEGIN
...
       SELECT count(sks.id) INTO flag FROM serial_keys sks WHERE sks.order_number = nro_order;
       IF flag = 0 THEN
...

Answer 2

The error message already tells you the immediate cause for the exception.

And mustaccio provided a related fix. But that's just putting lipstick on a pig. If you really need this kind of function, don't run a full count just to assert it is 0. That looks at every qualifying row in the table. Check with EXISTS instead, which can stop after finding the first qualifying row. Much cheaper. See:

• PL/pgSQL checking if a row exists

SO:

      IF NOT EXISTS (SELECT FROM serial_keys sks WHERE sks.order_number = nro_order) THEN
      ...

However, the function doesn't look right for several reasons.

If the loop runs out of rows, you raise an exception - after updating a row or not. This is expensive, achieving nothing, and it has to be rolled back, bloating the table with dead tuples.

There is no ORDER BY before LIMIT 1, so rows are picked in arbitrary order. The result is arbitrary. If the first pick happens to get no update, it might be picked over and over (or not). If some other row is picked first, the result may be different. contador is pretty meaningless. As is the row that's returned eventually. And you only update a single row at most anyway, since the first successful update prevents any further updates.

And all of this can be broken another time by race conditions from concurrent writes. I seriously doubt, your function does what it's supposed to do.

Going out on a limb, assuming you want to update the first (according to some undisclosed criteria) qualifying row (under concurrent write load?) if the given order number has not been assigned, yet:

WITH cte AS (
   SELECT id          -- pk column or any (set of) unique column(s)
   FROM   serial_keys sk
   WHERE  sk.id_game = _game_id
   AND    sk.state = 'Disponivel'
   AND    sk.order_number IS NULL
-- ORDER  BY ???   -- some deterministic sort order?
   LIMIT  1
   FOR    UPDATE SKIP LOCKED  -- you may or may not want this
   )
UPDATE serial_keys s
SET    order_number = nro_order
     , state        = 'enviado'
FROM   cte
WHERE  s.id = cte.id
AND    NOT EXISTS (
   SELECT FROM serial_keys sks
   WHERE  sks.order_number = nro_order
   )
RETURNING s.id, s.key;

NOT EXISTS (like the count in your original function) is still prone to race conditions. You may want to add a UNIQUE constraint or index on serial_keys(order_number), which is not.

See:

• Postgres UPDATE ... LIMIT 1