Question 223401 in PostgreSQL Question posted in
The official documentation can be found here.

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

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

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

    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:

    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$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search