I have the following trigger function & trigger in PostgreSQL 12.1:
create or replace function constraint_for_present()
returns trigger
as $$
BEGIN
if
new.present_status = 'viewing'
and new.name not in (select viewable_item from sourcing)
then raise exception 'a present_status of "viewing" requires that the viewable item is in sourcing';
end if;
return new;
END;
$$ language plpgsql;
create trigger constraint_for_present
before insert or update of present_status on viewable_item
for each row
execute function constraint_for_present();
These work as expected during data entry in the psql and TablePlus clients. However, the function throws an error when accessing the database via LibreOffice Base:
pq_driver: [PGRES_FATAL_ERROR]ERROR: relation "sourcing" does not exist
LINE 2: and new.name not in (select viewable_item from sourcing)
QUERY: SELECT new.present_status = 'viewing'
and new.name not in (select viewable_item from sourcing)
CONTEXT: PL/pgSQL function viewing.constraint_for_present() line 3 at IF
(caused by statement 'UPDATE "viewing"."viewable_item" SET "present_status" = 'none' WHERE "name" = 'test4'')
In Base I have a simple form set up for the trigger's table, with each foreign-key column set to list box, and the Type of list contents set to Sql (also tried Sql [Native]). The List content of each is (with appropriate table and primary key columns):
select name from viewing.cv_present_status order by name
(This database is using natural keys for now, for organizational political reasons.) The Bound field is set to 0, which is the displayed and primary key column.
So ... 2 questions:
In the trigger function, you can fully qualify the table
...
and new.name not in (select viewable_item from viewing.sourcing)
...