"ERROR: query has no destination for result data" for function with INSERT

Question

I have to create plpgsql function for generation sequence number considering some doc_id argument. I wrote a function, but when I run it I get error

[42601] ERROR: query has no destination for result data 
Where: PL/pgSQL function create_sequence_number(text) line 3 at SQL statement

My idea: the function gets the doc_id, saves or updates the existing entry, returns the value of sequence:

create or replace function create_sequence_number(doc_id TEXT, OUT _result BIGINT) RETURNS BIGINT AS
$$
BEGIN
    INSERT INTO _generated_sequences AS t (id, sequence)
    VALUES (CONCAT('document_sequence_', doc_id), 1)
    ON CONFLICT (id) DO UPDATE
        SET sequence = t.sequence + 1
    RETURNING _result = sequence;
END
$$ LANGUAGE 'plpgsql';

Now my function inserts sequence = 1 everywhere. I did it temporarily to solve the error mentioned above, in fact the function should save 1 only for new entries. For existing ones, the already existing sequence value should be increased by 1. But, as far as I know, it is not possible to use sequence + 1 like in this example:

INSERT INTO _generated_sequences AS t (id, sequence)
VALUES (CONCAT('document_sequence_', doc_id), sequence + 1)
ON CONFLICT (id) DO UPDATE
   SET sequence = t.sequence + 1
RETURNING _result = sequence;

[42703] ERROR: column "sequence" does not exist 
There is a column named "sequence" in table "t", but it cannot be referenced from this part of the query. 
PL/pgSQL function create_document_sequence_number(text) line 3 at SQL statement

`RETURNING _result = sequence` in incorrect syntax. It should be `RETURNING sequence INTO _result`. — Laurenz Albe, Aug 04 '23 at 11:49
But unfortunately my problem with value for `sequence` still there is. If I write `VALUES (CONCAT('document_sequence_', chat_id), EXCLUDED.sequence + 1)` it does not work — NeverSleeps, Aug 04 '23 at 11:53

Erwin Brandstetter · Accepted Answer · 2023-08-07T04:34:18.003

Laurenz already pointed out the immediate syntax issue. You must use the keyword INTO. See:

Get default serial value after INSERT inside PL/pgSQL

But don't use the solution in your answer. It is more expensive than necessary and, more importantly, breaks under concurrent load. The SELECT happens while the row is not locked yet. Multiple concurrent transactions might work off the same (outdated) state this way and return the same sequence number.

Also, sequence_value + 1 is noise for INSERT, which must insert 1 in any case. Only UPDATE increments the value.

This should work:

CREATE OR REPLACE FUNCTION pg_temp.create_sequence_number(doc_id text, OUT _result bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO _generated_sequences AS t
          (id                                  , sequence)
   VALUES (concat('document_sequence_', doc_id), 1       )
   ON     CONFLICT (id) DO UPDATE
   SET    sequence = t.sequence + 1
   RETURNING t.sequence
   INTO  _result;  -- INTO instead of "="
END
$func$;

The RETURNS clause is optional in combination with OUT parameters. See:

Can I make a plpgsql function return an integer without using a variable?

But a plain SQL function is simpler for this. Then you also don't need INTO to begin with:

CREATE OR REPLACE FUNCTION pg_temp.create_sequence_number(doc_id text)
  RETURNS bigint
  LANGUAGE sql AS
$func$
INSERT INTO _generated_sequences AS t
       (id                                  , sequence)
VALUES (concat('document_sequence_', doc_id), 1       )
ON     CONFLICT (id) DO UPDATE
SET    sequence = t.sequence + 1
RETURNING sequence;
$func$;

Thank you very much for your detailed explanation. Now everything is much clearer — NeverSleeps, Aug 07 '23 at 04:32

score 0 · Answer 2 · answered Aug 04 '23 at 13:09

Function that works for me in result:

create or replace function create_sequence_number(doc_id TEXT, OUT _result BIGINT) RETURNS BIGINT AS
$$
DECLARE
    sequence_value BIGINT;
    sequence_id TEXT;
BEGIN
    sequence_id = (CONCAT('document_sequence_', doc_id));
    sequence_value = (SELECT coalesce((select sequence from _generated_sequences where id = sequence_id), 0));

    INSERT INTO _generated_sequences AS t (id, sequence)
    VALUES (sequence_id, sequence_value + 1)
    ON CONFLICT (id) DO UPDATE
        SET sequence = t.sequence + 1
    RETURNING sequence into _result;
END
$$ LANGUAGE 'plpgsql';

"ERROR: query has no destination for result data" for function with INSERT

2 Answers2