PostgreSQL: Creating a Trigger that tries to do work on a non existing table

Question

as we start to migrate our Application from using Oracle to PostgreSQL we ran into the following problem:

A lot of our Oracle scripts create triggers that work on Oracle specific tables which dont exist in PostgreSQL. When running these scripts on the PG database they will not throw an error. Only when the trigger is triggered an error is thrown.

Example code:

-- Invalid query under PostgreSQL
select * from v$mystat;

-- Create a view with the invalid query does not work (as expected)
create or replace view Invalid_View as 
select * from v$mystat;

-- Create a test table
create table aaa_test_table (test timestamp);

-- Create a trigger with the invalid query does(!) work (not as expected)
create or replace trigger Invalid_Trigger 
before insert 
on aaa_test_table
begin
    select * from v$mystat;
end;

-- Insert fails if the trigger exists                                    
insert into aaa_test_table (test) values(sysdate);

-- Select from the test table
select * from aaa_test_table 
order by test desc;

Is there a way to change this behavior to throw an error on trigger creation instead?

Kind Regards, Hammerfels

Edit:

I was made aware, that we actually dont use basic PostgreSQL but EDB instead. That would probably explain why the syntax for create trigger seems wrong. I'm sorry for the confusion.

Answer 1

It will trigger an error, unless you have configured Postgres to postpone validation when creating functions.

Try issuing this before creating the trigger:

set check_function_bodies = on;

Creating the trigger should show

ERROR:  syntax error at or near "trigger"
LINE 1: create or replace trigger Invalid_Trigger