IF statement WITH inside postgresql

Question

I got this

DECLARE
inserted_user authentication.users;
inserted_favorite user_settings.favorite_beneficiary;    
BEGIN
        IF $6 = 'xxxxx-xxx-xx-xxxx-xxxxxxxxxxxx' THEN
            WITH inserted_user AS (
                INSERT INTO authentication.users (id, email, first_name, last_name, phone_number, role_id, additional_information)
                VALUES ($1, $2, $3, $4, $5, $6, $7)
                ),inserted_favorite AS(
                INSERT INTO user_settings.favorite_beneficiary (user_id, beneficiary_id)
                VALUES ($1, $8)
                )
        END IF;
        RAISE EXCEPTION 'unauthorized';
    END;

But I got this message error, ERROR: syntax error at or near "END" LINE 16: END IF;

Did I miss something ? Can I write it on this way ?

You're missing a query after your WITH, a CTE must be followed by a SELECT/INSERT/... — Marth
– Marth, Commented Oct 28, 2020 at 9:05

user330315 · Accepted Answer · 2020-10-28 09:08:28Z

3

Your CTE ("with clause") doesn't have a final query. You need to use:

WITH inserted_user AS (
    INSERT INTO authentication.users (id, email, first_name, last_name, phone_number, role_id, additional_information)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
) --<< no second CTE here
INSERT INTO user_settings.favorite_beneficiary (user_id, beneficiary_id)
VALUES ($1, $8)

answered Oct 28, 2020 at 9:08

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

IF statement WITH inside postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related