Search Postgresql Archives

Re: Passing a WHERE clause by trigger to a function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux