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?
Anyhow, like I said, time to show my ignorance. But it is a learning experience and ignorance is just a lack of knowledge. Failure to try to obtain missing knowledge is an entirely different thing altogether... Regards, Melvin On Mon, Jun 3, 2013 at 4:54 PM, David Johnston <pol...@yahoo.com> wrote: > 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. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
