Re: Passing a WHERE clause by trigger to a function

Melvin Call wrote
> 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

Conceptually what you are trying to do should not work. Why is the second
query invalid?

I suggest using a set of one or more functions to accomplish your goal into
a more structured way. This way you explicitly allow those filters that you
deem valid and exclude all others.

Update-able view triggers are intended to turn a view into something
resembling a (raw) table and users do not expect their syntactically valid
queries - referencing columns from the select-list - would result in an
error being raised simply by changing the where-clause.

There may be a way I am not aware of, my use of triggers is minimal, but I
really doubt it an question whether it would be a good idea to use said
functionality even if it exists.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Passing-a-WHERE-clause-by-trigger-to-a-function-tp5757825p5757843.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.