Sample Header Ad - 728x90

Postgres trigger check if insert affected column

0 votes
2 answers
152 views
I have a table where some columns have to be "protected", meaning they should not be written to by most users. I want to enforce this with a trigger function (as there is additional logic to the decision if the user , not relevant here). My problem is that I am not able to find out if a user **attempted to insert into** the **protected column**. My first idea was to check the value of the NEW variable. The problem: Columns can have default values of all kinds of types which would be included in the NEW, so a simple check for NULL (which could also be set by the user to overwrite a default value) will not do the trick. What I would like to avoid: - encoding the default value of the column into the code of the trigger function as this reduces maintainability and since I'm automatically generating the code for the trigger function and the column could have any data type the code generation could get pretty complex. - making a query to INFORMATION_SCHEMA to load the default of the column. Doing this for every insert seems pretty wasteful to me. One possible solution that I see: - making an IMMUTABLE function that accesses the INFORMATION_SCHEMA to load the default value for the column. As far as I understand postgres should only make the query once and not for every insert since the result of the function call is cached. Are there any better solutions? Is there any way to directly check if an INSERT affected a specific column?
Asked by J. Dietz (1 rep)
Aug 15, 2024, 11:13 AM
Last activity: Jul 24, 2025, 06:17 PM