Re: Passing a WHERE clause by trigger to a function

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 <polobo(at)yahoo(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>