Assume in Postgresql, I have a table T
and one of its column is C1
.
I want to trigger a function when a new record is adding to the table T
. The function should check the value of column C1
in the new record and if it is null/empty then set its value to 'X'
.
Is this possible?
The return value of a trigger function In row level BEFORE triggers, the return value has the following meaning: if the trigger returns NULL, the triggering operation is aborted, and the row will not be modified. for INSERT and UPDATE triggers, the returned row is the input for the triggering DML statement.
Syntax. CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ]; Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.
In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant'; When a string constant contains a single quote ('), you need to escape it by doubling up the single quote.
INSTEAD OF triggers do not support WHEN conditions. Typically, row-level BEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, a BEFORE trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent.
You are correct that you need a trigger, because setting a default value for the column won't work for you - default values only work for null
values and don't help you in preventing blank values.
In postgres there are a couple of steps to creating a trigger:
Step 1: Create a function that returns type trigger
:
CREATE FUNCTION my_trigger_function() RETURNS trigger AS $$ BEGIN IF NEW.C1 IS NULL OR NEW.C1 = '' THEN NEW.C1 := 'X'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql
Step 2: Create a trigger that fires before insert, which allows you to change values befre they are inserted, that invokes the above function:
CREATE TRIGGER my_trigger BEFORE INSERT ON T FOR EACH ROW EXECUTE PROCEDURE my_trigger_function()
And you're done.
See the above code executing on SQLFIddle demonstrating it working correctly!
You mention in a comment that the value 'X'
is retrieved from a subquery. If so, change the relevant line so something like:
NEW.C1 := (select some_column from some_table where some_condition);
It is possible but you are likely to be better off setting a default constraint on the column instead. When creating the table that would look like:
create table mytable as ( C1 thetype not null default X );
This says that if you add a row to the table and don't specify the value for C1 then X will be used instead. The not null is not necessary, but prevents updates from nulling that column assuming that's what you want.
EDIT: This only works for constant X, from your comments it seems there are two possible solutions.
Using a trigger would look something like this:
create function update_row_trigger() returns trigger as $$ begin if new.C1 is NULL then new.C1 := X; end if; return new; end $$ language plpgsql; create trigger mytrigger before insert on mytable for each row execute procedure update_row_trigger();
The new
variable in a trigger function is special, representing the row being inserted. Specifying the trigger as a before insert
trigger means you can modify the row before it is written to the table.
The second solution would be to use a computed column which Postgres defines in an unusual way:
create or replace function C1(row mytable) returns columntype immutable as $$ begin return X; -- where X is an expression using values from `row` end $$ language plpgsql;
This creates a function that takes a row of your table and returns a value, you can call it using . notation though, meaning you can do:
select *, t.C1 from mytable t;
The declaration of the function being immutable is optional, but it's needed if you want to index the "column". You would be able to index this column like this:
create index on mytable (C1(mytable));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With