Thread

1. It is doing my head in

   Blyth A J C (Comp) <ajcblyth@glam.ac.uk> — 1999-05-20T13:15:52Z

   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
   
   

2. Re: [HACKERS] It is doing my head in

   Jan Wieck <jwieck@debis.com> — 1999-05-20T13:40:46Z

   > 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) #