CODEWITHSUNDEEP
sqlpostgresql
MegaRoks
MegaRoks

Reputation: 948

Failed to insert record and return id

I have a query that returns its id after adding

DO
    $do$    
        BEGIN
            IF (SELECT COUNT(*) = 0 FROM COMMENTS WHERE PARENT_ID = ''${this.parent_id}') THEN 
                UPDATE COMMENTS SET HAS_CHILD = TRUE WHERE COMMENT_ID = '${this.parent_id}';
            END IF;
                INSERT INTO COMMENTS(USER_ID, ... , PARENT_ID,  ... , HAS_CHILD) VALUES('${this.user_id}', ... , ${this.parent_id}, ... ', FALSE)
                RETURNING COMMENT_ID;
        END
    $do$

When executing the request, an error occurs: ERROR: query has no destination for result data CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement SQL state: 42601. The request is successful without RETURNING COMMENT_ID;.

Why the request is not executed with RETURNING COMMENT_ID;?

Upvotes: 0

Views: 261

Answers (1)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6329

In PL/pgSQL returning values have to be assigned to a variable with the keyword INTO, see:
https://www.postgresql.org/docs/9.4/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

DO
  $do$
    DECLARE
      result INTEGER := 0;
    BEGIN
      IF (SELECT COUNT(*) = 0 FROM COMMENTS WHERE PARENT_ID = '${this.parent_id}') THEN 
        UPDATE COMMENTS SET HAS_CHILD = TRUE WHERE COMMENT_ID = '${this.parent_id}';
        -- Here you could specify returning value for the UPDATE branch...
      END IF;
      INSERT INTO COMMENTS(USER_ID, ... , PARENT_ID,  ... , HAS_CHILD) VALUES('${this.user_id}', ... , ${this.parent_id}, ... ', FALSE)
        RETURNING COMMENT_ID INTO result;
      RETURN result;
    END
$do$

Edit

Or you could use the RETURN QUERY syntax:

DO
  $do$
    BEGIN
      IF (SELECT COUNT(*) = 0 FROM COMMENTS WHERE PARENT_ID = '${this.parent_id}') THEN 
        UPDATE COMMENTS SET HAS_CHILD = TRUE WHERE COMMENT_ID = '${this.parent_id}';
        -- Here you could specify returning value for the UPDATE branch...
      END IF;
      RETURN QUERY
        INSERT INTO COMMENTS(USER_ID, ... , PARENT_ID,  ... , HAS_CHILD) VALUES('${this.user_id}', ... , ${this.parent_id}, ... ', FALSE)
          RETURNING COMMENT_ID;
    END
$do$

Upvotes: 1

Related Questions