CODEWITHSUNDEEP
sqlpostgresqlplpgsqlanonymous-function
Nikolas
Nikolas

Reputation: 44408

Pl/pgSQL anonymous code block fails on [42601] ERROR: query has no destination for result data

Assuming the simplified DO anonymous code block below in a sql script which I import on application start:

-- table definitions omitted
-- assume I want to use anonymous code because of looping

DO
$$
DECLARE
    parentId BIGINT;
    inputArray VARCHAR[][] := ARRAY [['a', 'A'], ['b', 'B'], ['c', 'C']];
    input VARCHAR[];
BEGIN
    FOREACH input SLICE 1 IN ARRAY inputArray
    LOOP
        INSERT INTO myParent (name) VALUES (input[1]) RETURNING id AS parentId;
        INSERT INTO myTable (id, name) VALUES (parentId, input[2]);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Upon import and executing the script, it fails on the following error:

[42601] ERROR: query has no destination for result data 
Where: PL/pgSQL function inline_code_block line 13 at SQL statement

I read the PostgreSQL DO statement documentation at https://www.postgresql.org/docs/current/sql-do.html and it says:

DO executes an anonymous code block, or in other words a transient anonymous function in a procedural language.

The code block is treated as though it were the body of a function with no parameters, returning void. It is parsed and executed a single time.

I would expect the INSERT statements within the DO anonymous code to execute with no hassle and go on. As said, it is a function with no parameters and returning void. Why it is expected result data and something to store them into?

Upvotes: 0

Views: 522

Answers (1)

Nikolas
Nikolas

Reputation: 44408

I have found out that the RETURNING causes the problem and confuses the return type of the entire block. The AS should be replaced with INTO and then the block starts to work flawlessly.

  • Erroneous: 
    INSERT INTO myParent (name) VALUES (input[1]) RETURNING id AS parentId;
  • Correct: 
    INSERT INTO myParent (name) VALUES (input[1]) RETURNING id INTO parentId;

Upvotes: 2

Related Questions