1

I have an application with PostgreSQL 9.4. Since the upsert is a feature of PostgreSQL 9.5, I need a workaround for that.

The task is additionally complicated by the necessity of returning the updated/inserted row.

At first I tried this:

    BEGIN;
    DO $$
    DECLARE
        _cursor CONSTANT refcursor := '_cursor';
    BEGIN
        LOOP
            OPEN _cursor FOR 
            UPDATE uac.roles
            SET       parent_id = null, 
                      PRIVILEGES = 'blablabla213', 
                      autoload = 'blablabla2' 
            WHERE     name = 'role_name2' 
            RETURNING *;
            IF FOUND THEN
                EXIT;
            END IF;
            CLOSE _cursor;
            BEGIN
                OPEN _cursor FOR
                INSERT INTO uac.roles
                    (
                        parent_id,
                        name,
                        PRIVILEGES,
                        autoload
                    )
                    VALUES
                    (
                        null,
                        'role_name2',
                        'blablabla214',
                        'blabla21'
                    )
                RETURNING *;
                EXIT;
            EXCEPTION
            WHEN unique_violation THEN
            END;
        END LOOP;
    END
    $$;
    FETCH ALL FROM _cursor;
    COMMIT;

But the internal variable "FOUND" will always be false, because the execution of the cursor happens just before the very end of the transaction, so it always gonna insert values.

UPDATE: Is it even possible without functions?

Dmitry Volkov
  • 1,347
  • 1
  • 18
  • 33
  • It's only possible without functions if you retry on conflict. – Craig Ringer Mar 03 '17 at 12:19
  • @CraigRinger can you elaborate on that please? – Dmitry Volkov Mar 03 '17 at 12:20
  • 1
    http://stackoverflow.com/q/1109061/330315 and http://stackoverflow.com/q/17267417/330315 –  Mar 03 '17 at 12:20
  • @a_horse_with_no_name yep, saw those, but I am not a fan of functions, so I asked a question hoping for possible solution without functions. – Dmitry Volkov Mar 03 '17 at 12:26
  • 1
    They also have solutions not using functions. But in either way you will have to catch errors _somewhere_ –  Mar 03 '17 at 12:27
  • @a_horse_with_no_name thanks, but I failed to find a solution which is inside one transaction, returns inserted values and not using functions; this is why I asked the question. – Dmitry Volkov Mar 03 '17 at 12:31
  • 1
    http://stackoverflow.com/a/8702291/330315 just add a `returning` clause to the final insert –  Mar 03 '17 at 12:44
  • @a_horse_with_no_name nice, thx buddy. – Dmitry Volkov Mar 03 '17 at 12:57
  • @a_horse_with_no_name actually, if I add a returning clause to the final insert, I still won't be able to get the values that were updated. – Dmitry Volkov Mar 03 '17 at 13:27
  • 1
    If only one row is affected at most, you could `DECLARE _row uac.roles` and select into `_row` (f.ex. with the syntax `_row := (INSERT/UPDATE ... RETURNING *);`. Then, you could open the cursor portal, like `OPEN _cursor FOR SELECT (_row).*`. – pozs Mar 06 '17 at 13:14

0 Answers0