Re: why is there no TRIGGER ON SELECT ?

Melvin: The proposal is to do something of the form

SELECT * FROM selecting_function()

And have selecting_function() perform any necessary auditing.

I guess this work fairly well - as long as you remember to remove "SELECT"
privileges on the wrapped table from everyone and setup SECURITY DEFINER on
the function itself.

For a normal "SELECT FROM table WHERE" construct the query planner is able
to use indexes on "table" based upon the where clause. If you do "SELECT
FROM func() WHERE" does the entire table always get scanned/returned or are
indexes applied in this case as well? These (performance concerns) are the
main reason that using a wrapping function is not intuitive. It would also
require a slightly different paradigm for the end-user and would require
rewriting to make it work in an existing system that was directly accessing
tables before (I assume if access is being done via VIEWs that incorporating
the function calls into the views would work just fine).

David J

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Melvin Davidson
Sent: Tuesday, February 22, 2011 8:47 AM
To: Dmitriy Igrishin
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] why is there no TRIGGER ON SELECT ?

Dmitriy

>Why not use function which returns table and wrap the
>logging (auditing) code in it ?

Because to use a trigger function, you need a trigger, and as previously
stated, you cannot have a trigger on select. The same applies for a rule.

Melvin Davidson