Thread: Trigger function does not modify the NEW value
I have a trigger that is created like so:
create trigger tr_on_table_modified after insert or delete or update
on some_table for each row execute procedure on_table_modified();
CREATE OR REPLACE FUNCTION on_table_modified() RETURNS trigger LANGUAGE plpgsql $$
/* some code that does not modify any values but calls PERFORM on another function */
raise notice 'lowercasing %', new.email;
new.email = lower(new.email);
raise notice ' to %', new.email;
return new;
$$
I can see in the output the notices with the expected values, but the value in the updated record is not lower-cased.
update some_table
set email = 'IGAL@Lucee.org'
where id = 1;
> 00000: lowercasing IGAL@Lucee.org
> 00000: to igal@lucee.org
select email
from some_table
where id = 1;
> email |
> --------------|
> IGAL@Lucee.org|
Any ideas?
Thanks,
Igal Sapir
Lucee Core Developer
Lucee.org
On Sat, 29 Jun 2019 at 14:54, Igal @ Lucee.org <igal@lucee.org> wrote: > create trigger tr_on_table_modified after insert or delete or update > on some_table for each row execute procedure on_table_modified(); > I can see in the output the notices with the expected values, but the value in the updated record is not lower-cased. You'll need a BEFORE trigger, not an AFTER trigger. https://www.postgresql.org/docs/current/sql-createtrigger.html -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 6/28/2019 8:07 PM, David Rowley wrote: > On Sat, 29 Jun 2019 at 14:54, Igal @ Lucee.org <igal@lucee.org> wrote: >> create trigger tr_on_table_modified after insert or delete or update >> on some_table for each row execute procedure on_table_modified(); >> I can see in the output the notices with the expected values, but the value in the updated record is not lower-cased. > You'll need a BEFORE trigger, not an AFTER trigger. > https://www.postgresql.org/docs/current/sql-createtrigger.html *facepalm* I was trying to "save" on creating a separate trigger so I added that to the bottom of an existing one and missed that. Thanks David! Igal
I'm in a bit of a quandary. I'm trying to figure out how to have non-existent values appear as NULL (or using COALESCE, as 0). I have several tables (DDL and DML shown at the bottom of this post - and there is a fiddle available here: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=171d207d25981e0db15791e1684de802 The crux of the problem is that there is a joining table location_job CREATE TABLE location_job ( lj_loc INTEGER NOT NULL, lj_job INTEGER NOT NULL, lj_percent INTEGER DEFAULT 0 NOT NULL, &c... - see fiddle or below ); with entries as follows: INSERT INTO location_job VALUES (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing value (1, 1, 10) (2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10); Now the lj_job from the missing value corresponds to a job_type of Unknown. So, performing this query: SELECT tl.loc_key, tl.loc_coords, lj.lj_loc, lj.lj_job, lj.lj_percent, j.job_id, j.job_type FROM the_location tl JOIN location_job lj ON tl.loc_key = lj.lj_loc JOIN job j ON lj.lj_job = j.job_id ORDER BY tl.loc_key, j.job_id; Gives me the results: loc_keyloc_coordslj_loclj_joblj_percentjob_idjob_type 1coord_112102Unemployed 1coord_113103Blue collar 1coord_114104White collar Notice that (as expected) there is no result for job_type Unknown for location 1 - it's the missing record - all fine and dandy! However, what I want to do is to include a record in my resultset that has NULL (or 0) for job_type Unknown. You will be able to see from the fiddle that I have tried varying the order of the joins and the join types (LEFT OUTER, RIGHT OUTER, FULL OUTER), all to no avail. My question boils down to how do I get the first line of my resultset to be 1 coord_1 1 1 0 1 Unknown -- the 0 corresponds to the percent of Unknowns in the location coord_1. I would be grateful for an explanation of any answer as I'm trying to learn SQL and this really has me stumped. Should you require any further information, please don't hesitate to let me know, TIA, Pól... =========== DDL and DML =============== CREATE TABLE crime ( cr_rating VARCHAR (25) PRIMARY KEY ); INSERT INTO crime VALUES ('Low'), ('Medium'), ('High'); CREATE TABLE weather ( weather_type VARCHAR (50) PRIMARY KEY ); INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane'); CREATE TABLE job (job_id SERIAL PRIMARY KEY, job_type VARCHAR (30) NOT NULL); INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue collar'), ('White collar'), ('Manager'), ('Self-employed'); CREATE TABLE the_location ( loc_key SERIAL PRIMARY KEY, loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in your system loc_status INTEGER CHECK (loc_status IN (0,1)), loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10), loc_crime VARCHAR (25) NOT NULL, -- could use a CHECK CONSTRAINT, but use a lookup table instead -- note also that the types should match exactly loc_weather VARCHAR (50) NOT NULL, -- location_rating_tourism, -- same lookup table idea here (Good, Medium, Poor, Death-zone) -- location_rating_income, -- list of social classes table -- location_rating_jobs CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating), CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES weather (weather_type) ); INSERT INTO the_location (loc_coords, loc_status, loc_rating, loc_crime, loc_weather) VALUES ('coord_1', 0, 7, 'Medium', 'Rainy'), ('coord_2', 1, 7, 'High', 'Mild'); CREATE TABLE location_job ( lj_loc INTEGER NOT NULL, lj_job INTEGER NOT NULL, lj_percent INTEGER DEFAULT 0 NOT NULL, PRIMARY KEY (lj_loc, lj_job), CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key), CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id) ); INSERT INTO location_job VALUES (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing value (1, 1, 10) (2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10); -- and my first query - see fiddle for others SELECT tl.loc_key, tl.loc_coords, lj.lj_loc, lj.lj_job, lj.lj_percent, j.job_id, j.job_type FROM the_location tl JOIN location_job lj ON tl.loc_key = lj.lj_loc JOIN job j ON lj.lj_job = j.job_id ORDER BY tl.loc_key, j.job_id;
Le sam. 29 juin 2019 à 17:20, Pól Ua Laoínecháin <linehanp@tcd.ie> a écrit : > I'm in a bit of a quandary. I'm trying to figure out how to have > non-existent values appear as NULL (or using COALESCE, as 0). Please ignore this question here - I have reposted it to pg-novice where I think it better belongs. TIA and rgs, Pól...
On 6/29/19 9:20 AM, Pól Ua Laoínecháin wrote: > I'm in a bit of a quandary. I'm trying to figure out how to have > non-existent values appear as NULL (or using COALESCE, as 0). > > I have several tables (DDL and DML shown at the bottom of this post - > and there is a fiddle available here: > https://dbfiddle.uk/?rdbms=postgres_11&fiddle=171d207d25981e0db15791e1684de802 As a start added to above fiddle: WITH dist AS (SELECT distinct on(job_id, loc_key) job_id, loc_key FROM the_location, job ) SELECT loc_key, job_id, COALESCE(lj_percent, 0) AS percent FROM dist LEFT JOIN location_job AS lj ON dist.job_id = lj.lj_job AND dist.loc_key = lj.lj_loc ORDER BY loc_key, job_id; loc_key | job_id | percent ---------+--------+--------- 1 | 1 | 0 1 | 2 | 10 1 | 3 | 10 1 | 4 | 10 1 | 5 | 10 1 | 6 | 50 2 | 1 | 30 2 | 2 | 30 2 | 3 | 10 2 | 4 | 10 2 | 5 | 10 2 | 6 | 10 > > The crux of the problem is that there is a joining table location_job > > CREATE TABLE location_job > ( > lj_loc INTEGER NOT NULL, > lj_job INTEGER NOT NULL, > lj_percent INTEGER DEFAULT 0 NOT NULL, > &c... - see fiddle or below > ); > > with entries as follows: > > INSERT INTO location_job > VALUES > (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing > value (1, 1, 10) > (2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10); > > Now the lj_job from the missing value corresponds to a job_type of Unknown. > > So, performing this query: > > SELECT > tl.loc_key, tl.loc_coords, > lj.lj_loc, lj.lj_job, lj.lj_percent, > j.job_id, j.job_type > FROM the_location tl > JOIN location_job lj ON tl.loc_key = lj.lj_loc > JOIN job j ON lj.lj_job = j.job_id > ORDER BY tl.loc_key, j.job_id; > > Gives me the results: > > loc_keyloc_coordslj_loclj_joblj_percentjob_idjob_type > 1coord_112102Unemployed > 1coord_113103Blue collar > 1coord_114104White collar > > Notice that (as expected) there is no result for job_type Unknown for > location 1 - it's the missing record - all fine and dandy! > > However, what I want to do is to include a record in my resultset that > has NULL (or 0) for job_type Unknown. > > You will be able to see from the fiddle that I have tried varying the > order of the joins and the join types (LEFT OUTER, RIGHT OUTER, FULL > OUTER), all to no avail. > > My question boils down to how do I get the first line of my resultset to be > > 1 coord_1 1 1 0 1 Unknown -- the 0 corresponds to the percent of > Unknowns in the location coord_1. > > I would be grateful for an explanation of any answer as I'm trying to > learn SQL and this really has me stumped. > > Should you require any further information, please don't hesitate to > let me know, > > TIA, > > Pól... > > > =========== DDL and DML =============== > > CREATE TABLE crime > ( > cr_rating VARCHAR (25) PRIMARY KEY > ); > > INSERT INTO crime VALUES ('Low'), ('Medium'), ('High'); > > CREATE TABLE weather > ( > weather_type VARCHAR (50) PRIMARY KEY > ); > > INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), > ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane'); > > CREATE TABLE job (job_id SERIAL PRIMARY KEY, job_type VARCHAR (30) NOT NULL); > > INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue > collar'), ('White collar'), ('Manager'), ('Self-employed'); > > CREATE TABLE the_location > ( > > loc_key SERIAL PRIMARY KEY, > loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in > your system > loc_status INTEGER CHECK (loc_status IN (0,1)), > loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10), > loc_crime VARCHAR (25) NOT NULL, > -- could use a CHECK CONSTRAINT, but use a lookup table instead > -- note also that the types should match exactly > loc_weather VARCHAR (50) NOT NULL, > -- location_rating_tourism, -- same lookup table idea here (Good, > Medium, Poor, Death-zone) > -- location_rating_income, -- list of social classes table > -- location_rating_jobs > CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating), > CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES > weather (weather_type) > ); > > INSERT INTO the_location > (loc_coords, loc_status, loc_rating, loc_crime, loc_weather) > VALUES > ('coord_1', 0, 7, 'Medium', 'Rainy'), > ('coord_2', 1, 7, 'High', 'Mild'); > > CREATE TABLE location_job > ( > lj_loc INTEGER NOT NULL, > lj_job INTEGER NOT NULL, > lj_percent INTEGER DEFAULT 0 NOT NULL, > PRIMARY KEY (lj_loc, lj_job), > CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key), > CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id) > ); > > INSERT INTO location_job > VALUES > (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50), -- missing > value (1, 1, 10) > (2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10); > > -- and my first query - see fiddle for others > > SELECT > tl.loc_key, tl.loc_coords, > lj.lj_loc, lj.lj_job, lj.lj_percent, > j.job_id, j.job_type > FROM the_location tl > JOIN location_job lj ON tl.loc_key = lj.lj_loc > JOIN job j ON lj.lj_job = j.job_id > ORDER BY tl.loc_key, j.job_id; > > > -- Adrian Klaver adrian.klaver@aklaver.com