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

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

> Hello
>
>
>
>
> On 05/04/2008, Guillaume Bog <guibog(at)gmail(dot)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
>
> >
> >
>