It is doing my head in
Ok all here is an question for you.
I am running pgsql on a linux 5.1 box.
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: postgres
postgres=> create table smile (
postgres-> s1 integer,
postgres-> s2 integer );
CREATE
postgres=> insert into smile values ( 1 , 2 );
INSERT 17866 1
postgres=> insert into smile values ( 3 , 4 );
INSERT 17867 1
postgres=> select * from smile
postgres-> ;
s1|s2
--+--
1| 2
3| 4
(2 rows)
postgres=> create function ttt() returns integer
postgres-> as 'select 4 as result'
postgres-> language 'sql' ;
CREATE
postgres=> select * from smile where s2=ttt() ;
s1|s2
--+--
3| 4
(1 row)
postgres=> create trigger trg1 after insert on smile for each row
postgres-> execute procedure ttt() ;
ERROR: CreateTrigger: function ttt () does not exist
postgres=> \q
So my question is - why does the create trigger function fail when the
function does in
fact exist ?
Andrew
postgres=> create trigger trg1 after insert on smile for each row
postgres-> execute procedure ttt() ;
ERROR: CreateTrigger: function ttt () does not exist
postgres=> \qSo my question is - why does the create trigger function fail when the
function does in
fact exist ?
The procedure called from a trigger has to return opaque (the triggering
tuple).
The elog could probably be modified to:
ERROR: CreateTrigger: function ttt () returning opaque does not exist
to help find your error.
Andreas
Import Notes
Resolved by subject fallback
So my question is - why does the create trigger function fail when the
function does in
fact exist ?
In fact - it does NOT exist!
First of all, the builtin 'sql' language cannot be used to
create triggers. This must be done in C or one of the
procedural languages PL/pgSQL and PL/Tcl.
The reason why the function doesn't exist is because a
trigger procedure is a function declared with no arguments
and a return type of OPAQUE. Except for the C language,
functions in PostgreSQL can be overloaded. Multiple different
functions can have the same name as long as their arguments
differ.
In reality trigger procedures take arguments. They are
defined at CREATE TRIGGER time. And they return one or no
database row of the table they are actually fired for.
The documentation how to create triggers is in chapters 11
and 13 of the PostgreSQL programmers manual.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
ZEUGSWETTER Andreas IZ5 <Andreas.Zeugswetter@telecom.at> writes:
The procedure called from a trigger has to return opaque (the triggering
tuple).
The elog could probably be modified to:ERROR: CreateTrigger: function ttt () returning opaque does not exist
to help find your error.
I think two separate messages would be better... will fix it.
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofThu20May1999153041+0200219F68D65015D011A8E000006F8590C60267B370@sdexcsrv1.f000.d0188.sd.spardat.at | Resolved by subject fallback