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?