Trigger function does not modify the NEW value

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&amp;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&amp;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