Thread: pl/pgsql RECORD data type, how to access to the values

Hello,

I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as argument to the trigger function.

Provided my table has only one column named 'id', I can do easilly

CREATE FUNCTION ft() RETURNS trigger AS $$
  BEGIN
  RAISE NOTICE 'It works:%', OLD.id;
  END
$$ LANGUAGE plpgsql;

But I'd like to do

CREATE FUNCTION ft() RETURNS trigger AS $$
  DECLARE
    col VARCHAR;
  BEGIN
    col = TG_ARGV[0]
    RAISE NOTICE 'This does not works:%', OLD.col
    RAISE NOTICE 'This also does not works:%', OLD[col]
  END
$$ LANGUAGE plpgsql;

I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I checked the docs.

Hello

On 05/04/2008, Guillaume Bog <guibog@gmail.com> wrote:
> Hello,
>
> I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as
> argument to the trigger function.
>
> Provided my table has only one column named 'id', I can do easilly
>
> CREATE FUNCTION ft() RETURNS trigger AS $$
>    BEGIN
>   RAISE NOTICE 'It works:%', OLD.id;
>   END
> $$ LANGUAGE plpgsql;
>
> But I'd like to do
>
> CREATE FUNCTION ft() RETURNS trigger AS $$
>   DECLARE
>     col VARCHAR;
>   BEGIN
>     col = TG_ARGV[0]
>     RAISE NOTICE 'This does not works:%', OLD.col
>     RAISE NOTICE 'This also does not works:%', OLD[col]
>   END
>  $$ LANGUAGE plpgsql;
>
> I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I
> checked the docs.

It's not possible in plpgsql. You have to use plperl, pltcl or plpython.

Regards
Pavel Stehule

>
>

On Sat, Apr 5, 2008 at 4:05 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

> Hello
>
>
>
>
> On 05/04/2008, Guillaume Bog <guibog@gmail.com> wrote:
> > Hello,
> >
> > I'm writing a trigger in pl/pgSQL and I'd like to pass one column name as
> > argument to the trigger function.
> >
> > Provided my table has only one column named 'id', I can do easilly
> >
> > CREATE FUNCTION ft() RETURNS trigger AS $$
> >    BEGIN
> >   RAISE NOTICE 'It works:%', OLD.id;
> >   END
> > $$ LANGUAGE plpgsql;
> >
> > But I'd like to do
> >
> > CREATE FUNCTION ft() RETURNS trigger AS $$
> >   DECLARE
> >     col VARCHAR;
> >   BEGIN
> >     col = TG_ARGV[0]
> >     RAISE NOTICE 'This does not works:%', OLD.col
> >     RAISE NOTICE 'This also does not works:%', OLD[col]
> >   END
> >  $$ LANGUAGE plpgsql;
> >
> > I tried OLD.(col) and other tricks, like "SELECT INTO" or "EXECUTE", and I
> > checked the docs.
>
> It's not possible in plpgsql. You have to use plperl, pltcl or plpython.

Ok, thanks. I may keep my code in the previous state instead because I
only have a little bit of duplication that currently still fits on one
screen, and it seems preferable to use pl/pgsql in my case.

Another question that is puzzling me:

I want a table to be "read-only", so I raise exceptions with a before
trigger on update, insert and delete. It works well.

This read-only table is modified (delete + insert) by a trigger
function set on another table, but this second trigger calls the first
and I can modify my read-only table. I'd like my "read-only" trigger
to be aware that the modification call on the read-only table comes
from the second trigger. Is it possible?

I have read in some places that I should use a rule instead, but I
never used them and it seems complex. I would prefer not to set up
complex access rules with GRANT and REVOKE because my access rules in
simple and works now. The best solution I can think of so far is to
have the client application work with a view, but having this behavior
fully managed through triggers would be more natural and I fear I
missed something in the docs.

> Regards
> Pavel Stehule
>
> >
> >
>

>
>  This read-only table is modified (delete + insert) by a trigger
>  function set on another table, but this second trigger calls the first
>  and I can modify my read-only table. I'd like my "read-only" trigger
>  to be aware that the modification call on the read-only table comes
>  from the second trigger. Is it possible?

PostgreSQL call triggers in alphabet order

>
>  I have read in some places that I should use a rule instead, but I
>  never used them and it seems complex. I would prefer not to set up
>  complex access rules with GRANT and REVOKE because my access rules in
>  simple and works now. The best solution I can think of so far is to
>  have the client application work with a view, but having this behavior
>  fully managed through triggers would be more natural and I fear I
>  missed something in the docs.
>

it's depend on application

Pavel

>  > Regards
>  > Pavel Stehule
>  >
>  > >
>  > >
>  >
>

Guillaume Bog wrote:

> I want a table to be "read-only", so I raise exceptions with a before
> trigger on update, insert and delete. It works well.
>
> This read-only table is modified (delete + insert) by a trigger
> function set on another table, but this second trigger calls the first
> and I can modify my read-only table. I'd like my "read-only" trigger
> to be aware that the modification call on the read-only table comes
> from the second trigger. Is it possible?

Quick question: Why not make the read only table a view of the writeable
table, instead of using triggers to copy data?

If your data doesn't fit that use or that'd be inefficient, can you use
access privileges rather than a trigger to limit changes to the read
only table? I find that limiting a user to SELECT priveleges on a table
and using a SECURITY DEFINER trigger or other function to perform
certain restricted priveleged operations on the table to be very useful.
In your case you might be able to restrict users to SELECT priveleges on
your read only table, drop the "read only" restriction trigger, and make
the updating trigger SECURITY DEFINER (after carefully thinking about
possible risks and issues).

Why the separate read only table, anyway? A materialized view / summary
table? Something to do with user access control ?

--
Craig Ringer

On Mon, Apr 7, 2008 at 1:56 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> Guillaume Bog wrote:
>
>  > I want a table to be "read-only", so I raise exceptions with a before
>  > trigger on update, insert and delete. It works well.
>  >
>  > This read-only table is modified (delete + insert) by a trigger
>  > function set on another table, but this second trigger calls the first
>  > and I can modify my read-only table. I'd like my "read-only" trigger
>  > to be aware that the modification call on the read-only table comes
>  > from the second trigger. Is it possible?
>
>  Quick question: Why not make the read only table a view of the writeable
>  table, instead of using triggers to copy data?

That's how it is now. I have a writable table of events on objects,
and a (complex) view depicting the status of my objects according to
those events. I have pushed a lot of logic on SQL side, mostly with
views, and I'm very happy with this design choice so far (I can
completely change my core logic in few hours!), but the drawback is
that selecting lists of objects according to their states becomes
slower (one second or more). So I tried to materialize one of the
status view, and it seems to work well, but I'd just want to make sure
nobody alters it. It seems that using privileges and SECURITY DEFINER
will be the best approach to protect data integrity.

I have a related issue with a value very often accessed by my object,
and available only in the "grand grand-parents", which makes a triple
join very common in many statements. I hid the triple join behind a
view for convenience but the performance is so-so (even with indexes
on each foreign keys), and I want to try to denormalize this value,
and duplicate it in the object. So here also I need triggers, and will
try the SECURITY DEFINER option (while it is different, because I need
to make only one column "read-only", not a full table)

Thanks for the tip.

>  If your data doesn't fit that use or that'd be inefficient, can you use
>  access privileges rather than a trigger to limit changes to the read
>  only table? I find that limiting a user to SELECT priveleges on a table
>  and using a SECURITY DEFINER trigger or other function to perform
>  certain restricted priveleged operations on the table to be very useful.
>  In your case you might be able to restrict users to SELECT priveleges on
>  your read only table, drop the "read only" restriction trigger, and make
>  the updating trigger SECURITY DEFINER (after carefully thinking about
>  possible risks and issues).
>
>  Why the separate read only table, anyway? A materialized view / summary
>  table? Something to do with user access control ?
>
>  --
>  Craig Ringer
>