Re: Passing a WHERE clause by trigger to a function - Mailing list pgsql-general
| From | Melvin Call |
|---|---|
| Subject | Re: Passing a WHERE clause by trigger to a function |
| Date | |
| Msg-id | CADGQN57GJBymRWjP2rnqzfOwJWTsE00kT0zE9kfMpht8KOVW2g@mail.gmail.com Whole thread Raw |
| In response to | Re: Passing a WHERE clause by trigger to a function (David Johnston <polobo@yahoo.com>) |
| Responses |
Re: Passing a WHERE clause by trigger to a function
(David Johnston <polobo@yahoo.com>)
|
| List | pgsql-general |
Hi David, and thanks for the response!
Like you, I think I have already come to the conclusion that to do what I want (to limit view modifications to a single row) will require a more detailed function. And my choice of invalid was definitely incorrect. Thanks for pointing that out. A better term would be restrict? What I am trying to do is allow modifications through the view where a person entity can be removed as an employee but remain in the person table, and a person can change departments/companies. But I only want to act on a single person at a time so the multi-faceted function appears to be the only solution. But I still have a question in that I'd like to know if I can pass the WHERE clause to the function so it can examine the query? Or will I have to test for the potential of acting on more than one row?On Mon, Jun 3, 2013 at 4:54 PM, David Johnston <polobo@yahoo.com> wrote:
Melvin Call wrote> DELETE FROM staffConceptually what you are trying to do should not work. Why is the second
> 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
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: