CODEWITHSUNDEEP
postgresqlviewupsertpostgresql-14
Norling
Norling

Reputation: 1171

postgresql upsert on view with triggers

I'm working with a postgresql 14 database where I have access to data through a view schema, and inserts and updates are performed with triggers. There is a unique constraint on the storage table, and I was wondering if there is a way to do upserts in this case?

This replicates the problem in a (much) smaller database:

schema:

CREATE SCHEMA storage;

-- unreachable storage schema

CREATE TABLE storage.data (
    id  SERIAL PRIMARY KEY,
    a INTEGER,
    b INTEGER,
    c INTEGER,
    CONSTRAINT pair UNIQUE(a, b)
);

-- reachable access schema

CREATE SCHEMA access;

CREATE VIEW access.data AS
    SELECT id, a, b, c FROM storage.data;

-- data insertion trigger for the access view

CREATE FUNCTION data_insert()
RETURNS TRIGGER AS $data_insert$
    BEGIN
        INSERT INTO storage.data (a, b, c)
            VALUES (NEW.a, NEW.b, NEW.c);
        RETURN NEW;
    END;
$data_insert$ LANGUAGE plpgsql;

CREATE TRIGGER data_insert_trigger
    INSTEAD OF INSERT ON access.data
    FOR EACH ROW EXECUTE PROCEDURE data_insert();

-- data update trigger for the access view

CREATE FUNCTION data_update()
RETURNS TRIGGER AS $data_update$
    BEGIN
        UPDATE storage.data SET
            a = NEW.a,
            b = NEW.b,
            c = NEW.c
            WHERE id = OLD.id;

        RETURN NEW;
    END;
$data_update$ LANGUAGE plpgsql;

CREATE TRIGGER data_update_trigger
    INSTEAD OF UPDATE ON access.data
    FOR EACH ROW EXECUTE PROCEDURE data_update();

What I would like to do is:

# INSERT INTO access.data(a,b,c) VALUES (1,2,3);
INSERT 0 1
# INSERT INTO access.data(a,b,c) VALUES (1,2,4) 
ON CONFLICT ON CONSTRAINT pair 
DO UPDATE SET c=EXCLUDED.c;
ERROR:  constraint "pair" for table "data" does not exist

Is there any way to do an upsert query in this situation, or should I settle for doing a select followed by insert or update?

EDIT: I can not modify the schemas or add functions, I can only make queries to the access schema.

Upvotes: 0

Views: 101

Answers (1)

Miles Elam
Miles Elam

Reputation: 1778

Have you tried something like this?

CREATE FUNCTION data_insert()
        RETURNS TRIGGER
       LANGUAGE plpgsql
             AS $data_insert$
 BEGIN
       INSERT INTO storage.data (a, b, c)
            VALUES ( NEW.a, NEW.b, NEW.c )
       ON CONFLICT DO UPDATE
                   a = EXCLUDED.a
                 , b = EXCLUDED.b
                 , c = EXCLUDED.c
       ;
       RETURN NEW;
  END;
$data_insert$;

Upvotes: 2

Related Questions