The following bug has been logged online:
Bug reference: 2087
Logged by: Jozef Behran
Email address: [EMAIL PROTECTED]
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
