Passing a WHERE clause by trigger to a function

From: Melvin Call <melvincall979(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Passing a WHERE clause by trigger to a function
Date: 2013-06-03 18:55:17
Message-ID: CADGQN54h8AdykPkOKA39MihLy+SdPLfuh5oVV7zpbEofOUXt0Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello list,

I am playing around with views and INSTEAD OF triggers, and have run across
an interesting problem that I can't seem resolve, so I figured it was time
to expose my ignorance to the whole world.

I have several tables:
person, consisting of a person's name and address, with a person_id as PK
company, consisting of a company name and address, with a company_id as PK
department, consisting of a department name and address, with a
department_id as PK, and an FK to it's associated company
role, consisting of a job title and description, with role_id as PK
staff, consisting of a person_id FK from person as the PK, a FK to their
role, and a FK to their department

I've created a view that joins the various tables to give the person's
name, the department that employs them, and the company that the department
is part of. I want to be able to delete a person entry from the staff table
through the view, and I have figured that out with a procedure that is
called by an INSTEAD OF trigger:

IF (TG_OP = 'DELETE') THEN
DELETE FROM staff
WHERE person_id =
(SELECT person_id
FROM person
WHERE person_lastname = OLD.last AND person_firstname = OLD.first);

Where OLD.last and OLD.first are the view column aliases.

So the following statement will delete from just the staff table the row
associated with the person_id, and will delete everything from the view too:
DELETE FROM staff
WHERE last = 'Doe' AND first = 'John';

This deletes the single record for John Doe (knowing it would delete
multiples if there were multiple John Doe in the table).

But, if I issue the following statement:
DELETE FROM staff
WHERE company_name = 'company1';

all staff records associated with company1 are deleted. I want the first
statement to succeed, but the second to fail in such a way that I can
capture it and handle it. Is it possible that when the trigger is fired to
pass to the function the WHERE clause from the DELETE statement, or
something along that line? Or am I looking at this problem all wrong?

Thanks,
Melvin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Call 2013-06-03 21:44:54 Re: Passing a WHERE clause by trigger to a function
Previous Message Ian Lawrence Barwick 2013-06-02 21:05:05 Re: Differences in Unicode handling on Mac vs Linux?