"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

Answer 1

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$;