Обсуждение: 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 queryYou are currentlyconnected 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| 23| 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
> 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) #