PostgreSQL Bugs - BUG #2087: Bogus error message on CREATE TRIGGER with a SQL function

Author

BUG #2087: Bogus error message on CREATE TRIGGER with a SQL function

Jozef Behran

2005-12-02, 3:24 am

The following bug has been logged online:

Bug reference: 2087
Logged by: Jozef Behran
Email address: jozef.behran@krs.sk
PostgreSQL version: 8.1.0
Operating system: Short answer: Mandrake 10.2. Long answer: Linux sarkan
2.6.11-6mdk #1 Tue Mar 22 16:04:32 CET 2005 i686 AMD Athlon(tm) Processor
unknown GNU/Linux
Description: Bogus error message on CREATE TRIGGER with a SQL
function
Details:

Here is a sample shell session showing the SQL file and
the output:

$ cat bug.sql
\set VERBOSITY verbose

BEGIN;

CREATE table pokus (
id INT4,
sval VARCHAR(100),
sortsval VARCHAR(100),
PRIMARY KEY(id)
);

CREATE FUNCTION tpokus(pokus) RETURNS pokus as $$
SELECT $1.id,$1.sval,translate($1.sval,'abc','ABC');
$$ LANGUAGE SQL IMMUTABLE;

INSERT INTO pokus (id,sval) values (1,'aha');

SELECT tpokus(pokus.*) FROM pokus;

CREATE TRIGGER trigger_pokus
BEFORE INSERT OR UPDATE
ON pokus FOR EACH ROW
EXECUTE PROCEDURE tpokus()
;

ROLLBACK;
$ psql -f bug.sql
BEGIN
psql:bug.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index "pokus_pkey" for table "pokus"
CREATE TABLE
CREATE FUNCTION
INSERT 0 1
tpokus
-------------
(1,aha,AhA)
(1 row)

psql:bug.sql:22: ERROR: function tpokus() does not exist
LOCATION: LookupFuncName, parse_func.c:1192
ROLLBACK
$ _

The buggy error message is the line before `ROLLBACK' (the one saying that
`tpokus' does not exist). The problem is that there *is* a function named
`tpokus' (the previous SELECT executed the function without problems). If it
is impossible to use a SQL function in a trigger, the error message should
say so (for example ERROR: cannot use SQL function `name' in a trigger). If
it is possible to have SQL-only triggers but the code is wrong, you should
add a complete example of a SQL trigger into the documentation to show all
the nasty quirks that may get into the way (for example the documentation
says that the trigger must return `trigger' but it is impossible to declare
such a SQL function).

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

2005-12-02, 3:24 am

"Jozef Behran" <jozef.behran@krs.sk> writes:
> CREATE FUNCTION tpokus(pokus) RETURNS pokus as $$
> SELECT $1.id,$1.sval,translate($1.sval,'abc','ABC');
> $$ LANGUAGE SQL IMMUTABLE;

> CREATE TRIGGER trigger_pokus
> BEFORE INSERT OR UPDATE
> ON pokus FOR EACH ROW
> EXECUTE PROCEDURE tpokus()
> ;

> The buggy error message is the line before `ROLLBACK' (the one saying that
> `tpokus' does not exist). The problem is that there *is* a function named
> `tpokus'

But it has the wrong parameter list. Read the trigger documentation:
trigger functions never take parameters.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jim C. Nasby

2005-12-02, 8:25 pm

From http://www.postgresql.org/docs/8.1/.../triggers.html:
The trigger function must be defined before the trigger itself can be
created. The trigger function must be declared as a function taking no
arguments and returning type trigger. (The trigger function receives its
input through a specially-passed TriggerData structure, not in the form
of ordinary function arguments.)

On Thu, Dec 01, 2005 at 10:49:49PM +0000, Jozef Behran wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2087
> Logged by: Jozef Behran
> Email address: jozef.behran@krs.sk
> PostgreSQL version: 8.1.0
> Operating system: Short answer: Mandrake 10.2. Long answer: Linux sarkan
> 2.6.11-6mdk #1 Tue Mar 22 16:04:32 CET 2005 i686 AMD Athlon(tm) Processor
> unknown GNU/Linux
> Description: Bogus error message on CREATE TRIGGER with a SQL
> function
> Details:
>
> Here is a sample shell session showing the SQL file and
> the output:
>
> $ cat bug.sql
> \set VERBOSITY verbose
>
> BEGIN;
>
> CREATE table pokus (
> id INT4,
> sval VARCHAR(100),
> sortsval VARCHAR(100),
> PRIMARY KEY(id)
> );
>
> CREATE FUNCTION tpokus(pokus) RETURNS pokus as $$
> SELECT $1.id,$1.sval,translate($1.sval,'abc','ABC');
> $$ LANGUAGE SQL IMMUTABLE;
>
> INSERT INTO pokus (id,sval) values (1,'aha');
>
> SELECT tpokus(pokus.*) FROM pokus;
>
> CREATE TRIGGER trigger_pokus
> BEFORE INSERT OR UPDATE
> ON pokus FOR EACH ROW
> EXECUTE PROCEDURE tpokus()
> ;
>
> ROLLBACK;
> $ psql -f bug.sql
> BEGIN
> psql:bug.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
> index "pokus_pkey" for table "pokus"
> CREATE TABLE
> CREATE FUNCTION
> INSERT 0 1
> tpokus
> -------------
> (1,aha,AhA)
> (1 row)
>
> psql:bug.sql:22: ERROR: function tpokus() does not exist
> LOCATION: LookupFuncName, parse_func.c:1192
> ROLLBACK
> $ _
>
> The buggy error message is the line before `ROLLBACK' (the one saying that
> `tpokus' does not exist). The problem is that there *is* a function named
> `tpokus' (the previous SELECT executed the function without problems). If it
> is impossible to use a SQL function in a trigger, the error message should
> say so (for example ERROR: cannot use SQL function `name' in a trigger). If
> it is possible to have SQL-only triggers but the code is wrong, you should
> add a complete example of a SQL trigger into the documentation to show all
> the nasty quirks that may get into the way (for example the documentation
> says that the trigger must return `trigger' but it is impossible to declare
> such a SQL function).
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links