Re: Auto-timestamp generator (attached) - Mailing list pgsql-general

When I try this in 7.0.3:

playpen=# \i temp.txt
CREATE
CREATE
playpen=# create table foo (a serial, b text, c timestamp);
NOTICE:  CREATE TABLE will create implicit sequence 'foo_a_seq' for
SERIAL column 'foo.a'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'foo_a_key' for
table 'foo'
CREATE
playpen=# select lastchg_addto('foo','c');
ERROR:  plpgsql: cache lookup from pg_proc failed
playpen=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)



That error (cache lookup from pg_proc failed) is the same one I got when
I was trying to implement this myself, and I still don't know what it
means.

Richard Huxton wrote:
>
> Following the 'new type proposal' discussion recently I decided to have a
> play at creating an automatic trigger generator. Attached is the sql and an
> example of its use.
>
> Basically you call a function:
>   select lastchg_addto(mytable,mycol);
> where mycol is of type timestamp. The function builds the
>
> To use it you will need plpgsql enabled (man createlang) and also version
> 7.1
> After use, there are two functions left - you can remove these with:
>   drop function lastchg_addto(text,text);
>   drop function lastchg_remove(text,text);
>
> I've tried to layout the plpgsql for ease of understanding - if you want to
> see how the trigger gets created, you can return exec1 or exec2 instead of
> the success message.
>
> This just a demo - obviously it's fairly simple to put together triggers for
> this purpose, but I'd appreciate any thoughts about the approach.
>
> TIA people
>
> Oh - 2 questions for any of the developers/clued up
>
> 1. Is there any way to parse a variable-length list of parameters in
> plpgsql?
> 2. Is there any chance of a different quoting method for functions? e.g.
>     create function ... as q[ ...body here ...];
>    So we can avoid the '''' stuff - it's a lot of static
>
> - Richard Huxton
>
>   ------------------------------------------------------------------------
>                      Name: lastchange.sql
>    lastchange.sql    Type: unspecified type (application/octet-stream)
>                  Encoding: quoted-printable
>
>                              Name: lastchange_example.txt
>    lastchange_example.txt    Type: Plain Text (text/plain)
>                          Encoding: quoted-printable

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com