2

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.

Shog9
  • 156,901
  • 35
  • 231
  • 235
Hammerfels
  • 21
  • 2
  • The code you have shown is not valid for Postgres to begin with –  Sep 18 '18 at 18:21
  • @a_horse_with_no_name could you elaborate that, please? – Hammerfels Sep 19 '18 at 07:28
  • [`create trigger`](https://www.postgresql.org/docs/current/static/sql-createtrigger.html) uses a completely different syntax in Postgres –  Sep 19 '18 at 07:33

1 Answers1

0

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
JGH
  • 15,928
  • 4
  • 31
  • 48
  • Changing this in the session didnt change the behavior in any way for me. Changing this in the postgresql.conf didnt change it either. Do you have another idea where i could look? I should probably mention that we actually use EDB instead of basic Postgresql. – Hammerfels Sep 19 '18 at 14:18