Auto-timestamp generator (attached)

I think that modules like this could be included in the distribution or
archieved at the ftp. They'd make it easier for people new to sql to
start using postgresql. Also there would be no performance loss in
the backend code, as these "modules" don't need any support.

- Einar

Quoting Einar Karttunen <ekarttun@cs.Helsinki.FI>:

I think that modules like this could be included in the distribution or
archieved at the ftp. They'd make it easier for people new to sql to
start using postgresql. Also there would be no performance loss in
the backend code, as these "modules" don't need any support.

This is what /contrib in the source is for ;-)

Peter

- Einar

On Thu, 8 Feb 2001, 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

--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

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

Joseph Shraibman <jks@selectacast.net> writes:

playpen=# select lastchg_addto('foo','c');
ERROR: plpgsql: cache lookup from pg_proc failed

Somewhere you've got a stored plan --- probably a view or rule or
trigger, if it persists across backend runs --- that refers to a plpgsql
function that no longer exists (at least not under the same OID). You
need to drop and recreate that view or whatever. Unfortunately you
haven't shown us enough info to guess which one...

regards, tom lane

Tom Lane wrote:

Joseph Shraibman <jks@selectacast.net> writes:

playpen=# select lastchg_addto('foo','c');
ERROR: plpgsql: cache lookup from pg_proc failed

Somewhere you've got a stored plan --- probably a view or rule or
trigger, if it persists across backend runs --- that refers to a plpgsql
function that no longer exists (at least not under the same OID). You
need to drop and recreate that view or whatever. Unfortunately you
haven't shown us enough info to guess which one...

regards, tom lane

It is persisting accross backend restarts, and on a newly created
database too. There are no views, rules, or triggers on this database.

If this is involving rules, could this be related to the patched version
of command.c that I have (the patch was to fix an error when trying to
add foreign keys, see:
http://postgresql.readysetnet.com/mhonarc/pgsql-sql/2000-12/msg00057.html
)?

The patched command.c is at:
http://www.selectacast.net/~jks/postgres/command.c

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

Joseph Shraibman <jks@selectacast.net> writes:

It is persisting accross backend restarts, and on a newly created
database too. There are no views, rules, or triggers on this database.

Oh? That's interesting. It might help to patch
src/pl/plpgsql/src/pl_comp.c to show you the OID that it's unhappy about
... um ... waitasec. 7.0.* already *has* that patch. If you're getting
an error message that doesn't mention an OID, you must be running a
7.0.0 or older plpgsql. That might not play too well with a post-7.0
backend. Check the path that's defined for the plpgsql shlib.

regards, tom lane

No, this is 7.0.3. Look in my pervious message where I did a version().

Tom Lane wrote:

Joseph Shraibman <jks@selectacast.net> writes:

It is persisting accross backend restarts, and on a newly created
database too. There are no views, rules, or triggers on this database.

Oh? That's interesting. It might help to patch
src/pl/plpgsql/src/pl_comp.c to show you the OID that it's unhappy about
... um ... waitasec. 7.0.* already *has* that patch. If you're getting
an error message that doesn't mention an OID, you must be running a
7.0.0 or older plpgsql. That might not play too well with a post-7.0
backend. Check the path that's defined for the plpgsql shlib.

regards, tom lane

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

Joseph Shraibman <jks@selectacast.net> writes:

No, this is 7.0.3. Look in my pervious message where I did a version().

Yes, that proves that your core backend is 7.0.3. However, the spelling
of the error message proves that your plpgsql shlib is NOT 7.0.3. It
might well be 6.5 or even older.

regards, tom lane

Tom Lane wrote:

Joseph Shraibman <jks@selectacast.net> writes:

No, this is 7.0.3. Look in my pervious message where I did a version().

Yes, that proves that your core backend is 7.0.3. However, the spelling
of the error message proves that your plpgsql shlib is NOT 7.0.3. It
might well be 6.5 or even older.

regards, tom lane

Huh? How could that happen?

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

Joseph Shraibman <jks@selectacast.net> writes:

Yes, that proves that your core backend is 7.0.3. However, the spelling
of the error message proves that your plpgsql shlib is NOT 7.0.3. It
might well be 6.5 or even older.

Huh? How could that happen?

Easily. Check the path to the shlib that's defined in the CREATE
FUNCTION call for plpgsql_call_handler, eg do
select * from pg_proc where proname = 'plpgsql_call_handler';
The backend will believe whatever you tell it --- if, say, you restored
a 6.5 dump that had a different library path than your current
installation, you'd be in trouble. How exactly did you install plpgsql
support into this database?

regards, tom lane

Tom Lane wrote:

Joseph Shraibman <jks@selectacast.net> writes:

Yes, that proves that your core backend is 7.0.3. However, the spelling
of the error message proves that your plpgsql shlib is NOT 7.0.3. It
might well be 6.5 or even older.

Huh? How could that happen?

Easily. Check the path to the shlib that's defined in the CREATE
FUNCTION call for plpgsql_call_handler, eg do
select * from pg_proc where proname = 'plpgsql_call_handler';
The backend will believe whatever you tell it --- if, say, you restored
a 6.5 dump that had a different library path than your current
installation, you'd be in trouble. How exactly did you install plpgsql
support into this database?

regards, tom lane

CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

I just cut and pasted without looking at it. Stupid me. That points to
the 6.5.3 lib that came with redhat.

Now:
playpen=# select lastchg_addto('foo','c');
ERROR: fmgr_info: function 326368: cache lookup failed

There is nothing in pg_proc with oid of 326368.

But if try again in a newly created database:
playpen2=# CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
playpen2-# '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
CREATE
playpen2=#
playpen2=# CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
playpen2-# "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
CREATE
playpen2=# \i temp.txt
CREATE
CREATE
playpen2=# 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
playpen2=# select lastchg_addto('foo','c');
ERROR: parser: parse error at or near "execute"

Is there something in the script that is not compatible with 7.0.3?

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

Joseph Shraibman <jks@selectacast.net> writes:

The backend will believe whatever you tell it --- if, say, you restored
a 6.5 dump that had a different library path than your current
installation, you'd be in trouble. How exactly did you install plpgsql
support into this database?

CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

I just cut and pasted without looking at it. Stupid me. That points to
the 6.5.3 lib that came with redhat.

Ah, that explains a good deal. You were lucky that the 6.5.3 shlib was
just incompatible enough to fail cleanly, and not to do anything really
screwy :-(

BTW, the recommended way to crank up plpgsql or other PL languages is
to use the 'createlang' script, which has a slightly better chance of
getting this sort of detail right.

playpen2=# select lastchg_addto('foo','c');
ERROR: parser: parse error at or near "execute"

Is there something in the script that is not compatible with 7.0.3?

plpgsql's 'execute' feature is new for 7.1 ...

regards, tom lane

Tom Lane wrote:

BTW, the recommended way to crank up plpgsql or other PL languages is
to use the 'createlang' script, which has a slightly better chance of
getting this sort of detail right.

I got that piece of sql from someone on the sql list. These things
should be on:
http://www.postgresql.org/docs/postgres/c4091.htm

... and the doc pages for PL/tcl and PL/perl. I was having no idea why
the examples weren't working because the documentation neglected to
point out that I had to explicitly add the languages.

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

Joseph Shraibman <jks@selectacast.net> writes:

BTW, the recommended way to crank up plpgsql or other PL languages is
to use the 'createlang' script, which has a slightly better chance of
getting this sort of detail right.

I got that piece of sql from someone on the sql list. These things
should be on:
http://www.postgresql.org/docs/postgres/c4091.htm

... and the doc pages for PL/tcl and PL/perl. I was having no idea why
the examples weren't working because the documentation neglected to
point out that I had to explicitly add the languages.

The 7.1 docs do mention that more prominently, see
http://www.postgresql.org/devel-corner/docs/postgres/programmer-pl.htm
Peter Eisentraut has done a lot of good work on the docs over the last
few months...

regards, tom lane

Tom Lane wrote:

Joseph Shraibman <jks@selectacast.net> writes:

BTW, the recommended way to crank up plpgsql or other PL languages is
to use the 'createlang' script, which has a slightly better chance of
getting this sort of detail right.

I got that piece of sql from someone on the sql list. These things
should be on:
http://www.postgresql.org/docs/postgres/c4091.htm

... and the doc pages for PL/tcl and PL/perl. I was having no idea why
the examples weren't working because the documentation neglected to
point out that I had to explicitly add the languages.

The 7.1 docs do mention that more prominently, see
http://www.postgresql.org/devel-corner/docs/postgres/programmer-pl.htm
Peter Eisentraut has done a lot of good work on the docs over the last
few months...

I'm not sure I like the new docs. In the current (7.x) ones at least
there is one contents page for all the docs so I would be more likely to
find this stuff.

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

Joseph Shraibman writes:

I'm not sure I like the new docs. In the current (7.x) ones at least
there is one contents page for all the docs so I would be more likely to
find this stuff.

Yeah, that's going to be fixed within a day or two.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

As a suggestion regarding the docs ... as a newbie, I went straight to
the"user's lounge" and didn't check out the "developer's corner" because
I figured those docs would be pretty hard-core technical. But in
reality, many of the developer's docs are much the same, only more
current and more complete. I think I have seen other questions on this
listserv deriving from the same problem (they would be easily answered
from the developer's docs). IMHO, perhaps there should be some sort of a
pointer from the user's lounge to the developer's docs, with warning, if
that is necessary??

While I'm at it ... to show that I am super-anal-retentive ... the "l"
in "lounge" on the www.postgresql.org home page should be capitalized. :)

Cheers,

Laurel Williams
tech@clearwater-inst.com
Watertown, MA

Peter Eisentraut wrote:

As a suggestion regarding the docs ... as a newbie, I went straight to
the"user's lounge" and didn't check out the "developer's corner" because
I figured those docs would be pretty hard-core technical. But in
reality, many of the developer's docs are much the same, only more
current and more complete. I think I have seen other questions on this
listserv deriving from the same problem (they would be easily answered
from the developer's docs). IMHO, perhaps there should be some sort of a
pointer from the user's lounge to the developer's docs, with warning, if
that is necessary??

Can you give us an example of something you found in developers that was
not in users?

While I'm at it ... to show that I am super-anal-retentive ... the "l"
in "lounge" on the www.postgresql.org home page should be capitalized. :)

Oh, he got us there.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Laurel Williams writes:

As a suggestion regarding the docs ... as a newbie, I went straight to
the"user's lounge" and didn't check out the "developer's corner" because
I figured those docs would be pretty hard-core technical. But in
reality, many of the developer's docs are much the same, only more
current and more complete. I think I have seen other questions on this
listserv deriving from the same problem (they would be easily answered

from the developer's docs). IMHO, perhaps there should be some sort of a

pointer from the user's lounge to the developer's docs, with warning, if
that is necessary??

The documentation in the developer's corner is for the upcoming version
7.1, mostly for the benefit of developers that don't want to build it
themselves, whereas the one in the user's lounge are for the released
version 7.0 and earlier releases. The reason that they are mostly the
same is that the product they're describing is mostly the same. The
reason they are more complete is that people have taken the time to work
on them since 7.0 was released. And there is a link from the user's
lounge to the current development docs.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/