1

I have a postgres database with a postGIS extension. Some of the fields in the database tables are primary keys that are automatically being generated either with a UUID genarator or based on another column. Now, within a postgres/SQL IDE with a 'before insert on' trigger function this is no problem because it is generating the UUID before the insert.

However, I want to achieve the same within QGIS. But because of the NOT NULL characteristic of the PK I can't leave this field empty and thus not being able to save it. Is there a way to solve this?

4
  • Have you tried to use a dummy default UUID? The value would be overwritten by your trigger – JGH Nov 23 '20 at 14:55
  • @JGH good idea, but most of these fields are referencing UUID values from other tables via foreign keys. I can't insert random UUID's, it will error that it can't be found in the parent table. – Konan Pruiksma Nov 23 '20 at 15:29
  • Does the UUID field need to be PK or could it be another unique field? – user30184 Nov 23 '20 at 15:40
  • @user30184 it could be another I guess – Konan Pruiksma Nov 24 '20 at 10:30
2

A simple solution would be to

  1. proxy your DDLs through a 'working' View having an INSTEAD OF trigger in place:

    CREATE VIEW <main_table>_view AS (
      SELECT <uuid>,
             <link>,
             ...,
             geom::GEOMETRY(<TYPE>, <SRID>)     -- explicitly forcing registration of the geom column
      FROM   <main_table>
    );
    
    
    CREATE OR REPLACE FUNCTION <main_table>_view_insert_function()
    RETURNS TRIGGER LANGUAGE 'plpgsql' AS
      $$
      BEGIN
        SELECT fk.<uuid>
        FROM   <fk_table> AS fk
        WHERE  fk.<link> = NEW.<link>
        INTO   NEW.id
        ;
    
        INSERT INTO <main_table>
          VALUES (NEW.*)
        ;
      END;
      $$
    ;
    
    CREATE TRIGGER <main_table>_view_insert_trigger
      INSTEAD OF INSERT
      ON <main_table>
      FOR EACH ROW
      EXECUTE FUNCTION <main_table>_view_insert_function()
    ;
    
  2. deactivate Enforce not null constraint. in QGIS:

    <Layer properties> | Attributes Form | Available Widgets | Fields | <uuid>
    

    While the field is still alarmingly red then, the OK button is active.

The idea is to only ever load and work with that View in QGIS; this allows for plenty other things in terms of access control and data quality validation. UPDATES and DELETES should work without any triggers, since the View itself is updateable.


Note:

You will want to add

WHEN (NEW.<uuid> IS NULL)

to all BEFORE INSERT trigger (or add the respective filter in the function itself if this trigger does more complex data manipulation) if you need to keep them along with the working View, to remove it from the trigger chain when inserting via the View. While it does no harm, it will essentially duplicate the operation.

Alternatively, you can rewrite the Views trigger function to explicitly insert into all but the PK column, and have the BEFORE INSERT trigger handle the case:

CREATE OR REPLACE FUNCTION <main_table>_view_insert_function()
RETURNS TRIGGER LANGUAGE 'plpgsql' AS
  $$
  BEGIN
    INSERT INTO <main_table>(<link>, ..., geom)
      VALUES (NEW.<link>, ..., NEW.geom)
    ;
  END;
  $$
;

Personally, I'd go the non-chaining way, as

  • you only want to fire that BEFORE INSERT trigger on missing <uuid> values anyways (except it does more complex insertion logic, of course)
  • trigger chains add overhead, and can become hard to debug

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.