1

I have the following issue: I have a table on a PostgreSQL database which triggers its changes via dblink to another table in a different data base. The trigger function looks as follows:

CREATE OR REPLACE FUNCTION scheme.triggerfunctionname()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    str_execute text;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        str_execute := dblink_build_sql_insert('schema.table'::text, '1', 1, ARRAY[NEW.gid]::text[], ARRAY[NEW.gid]::text[]);
    ELSIF (TG_OP = 'UPDATE') THEN
        str_execute := dblink_build_sql_update('schema.table'::text, '1', 1, ARRAY[NEW.gid]::text[], ARRAY[OLD.gid]::text[]);
     ELSIF (TG_OP = 'DELETE') THEN
        str_execute := dblink_build_sql_delete('schema.table'::text, '1', 1, ARRAY[OLD.gid]::text[]);
    END IF;
    PERFORM dblink_exec('map', str_execute);
    RETURN NULL;
END; $function$
;

The triggering works well if I am doing the changes right at the database. But now I have the case, that I want to insert data in the main table via ogr2ogr from a WFS service.

In this case the trigger function does not work correctly, because it does the INSERT on public.tablename and not on schema.tablename.

So the insert via ogr2ogr in the main table works well but after that the syncronisation via the trigger function goes wrong.

Is there a difference between "selfmade" inserts on the table and inserts which come from another place (in this case ogr2ogr)?

2
  • Good question but maybe it belongs rather to DB Admins site.
    – user30184
    Commented Jun 27, 2022 at 6:32
  • thanks @user30184, yes you're right, it is not really a gis issue, so I will ask it there again.
    – danceb
    Commented Jun 27, 2022 at 9:54

1 Answer 1

1

I have found the solution. It was not a problem within the database, it was a missleading config of the ogr2ogr command. Actually it is possible to set the schema with the -nln parameter! But at this point the ogr2ogr documentation is not really clear for me, that it could be handled that way. It only says "Assign an alternate name to the new layer" and on the documentation of the PG Driver there is no example for this as well.

So setting -nln schema.table instead of -nln table -doo ACTIVE_SCHEMA=schema as I did works!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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