SEO

Hi,

I wanted to setup a simple "generic" type trigger. What a mean by generic
is that i don't want to hardcode the
NEW.<column> or OLD.<column> calls, i searched for a way to loop over
the NEW/OLD rowtypes but could not figure out how it should be done.

This led me to attempt to do it in a way which i think is
incorrect, first off i extract the column names for the given table
(TG_RELNAME) from the information schema like so

-- extract the column names from tab
FOR colname IN (SELECT column_name FROM information_schema.columns WHERE
table_name = tab) LOOP
colnames[counter] := colname.column_name;
counter := counter + 1;
END LOOP;

Where tab is TG_RELNAME, i then store them in a varchar array (i have
verified that the values contained are correct).

If the TG_OP is 'UPDATE' i then wanted to figure out what column had been
changed (at a later date i might also list the new and old vaules), so the
plan was to do something like this:

ELSIF (TG_OP = 'UPDATE') THEN
FOR i IN 1 .. counter LOOP
IF(NEW.colnames[i] <> OLD.colnames[i])THEN
-- only for testing, to see if a change in a column can be detected
values_changed := values_changed || colnames[i] || ' was changed';
END IF;
END LOOP;

-- values_changed currently contains rubbish
INSERT INTO audit VALUES (audit_id, 'UPDATE', values_changed, '', now(),
TG_RELNAME);
ELSE

The above code does nothing useful and is only to test out the concept. The
problem comes from NEW.colnames[i] because the NEW rowtype does not have a
column called colnames. Can anyone tell me how to append the value of
colnames[i] to NEW so it would work, or is it even possible to iterate of
the NEW/OLD rowtype without having to know the column names ?

Thanks in advance,

Andy

On Tue, Apr 24, 2007 at 05:35:09PM +0200, Andy Dale wrote:
> Hi,
>
> I wanted to setup a simple "generic" type trigger. What a mean by generic
> is that i don't want to hardcode the
> NEW.<column> or OLD.<column> calls, i searched for a way to loop over
> the NEW/OLD rowtypes but could not figure out how it should be done.

You're trying to do something that pl/pgsql it notoriously bad at. I
suggest you use some more dynamic language like perl/python/etc.

> The above code does nothing useful and is only to test out the concept. The
> problem comes from NEW.colnames[i] because the NEW rowtype does not have a
> column called colnames. Can anyone tell me how to append the value of
> colnames[i] to NEW so it would work, or is it even possible to iterate of
> the NEW/OLD rowtype without having to know the column names ?

Not in pl/pgsql (being statically typed). In some other languages you can.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGLiVUIB7bNG8LQkwRAgxpAJwJDamHyvdPt4a/vKQ7Xl1GK+AjFQCeNJIx
0gUkiVGcHGh6dxdHy/YeM+Y=
=vw4h
-----END PGP SIGNATURE-----

Andy Dale wrote:
> Hi,
>
> I wanted to setup a simple "generic" type trigger. What a mean by generic
> is that i don't want to hardcode the
> NEW.<column> or OLD.<column> calls, i searched for a way to loop over
> the NEW/OLD rowtypes but could not figure out how it should be done.

Don't use plpgsql - that's the secret. Try pltcl or plperl or similar.
PL/pgsql is a bit too "statically typed" for it.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thanks for the quick response, i think will try it with python or perl.


Cheers,

Andy

On 24/04/07, Richard Huxton <dev@archonet.com> wrote:
>
> Andy Dale wrote:
> > Hi,
> >
> > I wanted to setup a simple "generic" type trigger. What a mean by
> generic
> > is that i don't want to hardcode the
> > NEW.<column> or OLD.<column> calls, i searched for a way to loop over
> > the NEW/OLD rowtypes but could not figure out how it should be done.
>
> Don't use plpgsql - that's the secret. Try pltcl or plperl or similar.
> PL/pgsql is a bit too "statically typed" for it.
>
> --
> Richard Huxton
> Archonet Ltd
>