Thread

1. create trigger

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-14T23:04:34Z

   I am trying to use "triggers". I get an error after "create trigger".
   Does anybody know why?
   
    create function insert_into_db2 RETURNS int AS
   '/work/posgresql/lib/libpq.so' language 'C';
   
    create trigger trial before insert or update on db1user for each row
   execute procedure  insert_into_db2();
   
    ERROR:  CreateTrigger: function insert_into_db2() must return OPAQUE
   
   Does anybody know why?
   
   Also, if a function is enforced to return OPAQUE then  what is the
   "RETURNS" clause?
   
   
   One more question,
        If a trigger does not succeed then I want to ABORT transaction, is
   it possible? how?
   
   
   - Sandeep
   
   
   
   

2. Re: create trigger

   Robert B. Easter <reaster@comptechnews.com> — 2000-12-14T23:54:39Z

   Trigger functions, no matter what language, have be RETURNS OPAQUE.
   
   Here is an example (in the PostgreSQL docs):
   http://www.comptechnews.com/~reaster/postgres/triggers20290.htm
   
   Sorry I can't help more as I've never actually used C triggers, just PL/pgSQL 
   ones.
   
   
   On Thursday 14 December 2000 18:04, Sandeep Joshi wrote:
   > I am trying to use "triggers". I get an error after "create trigger".
   > Does anybody know why?
   >
   >  create function insert_into_db2 RETURNS int AS
   > '/work/posgresql/lib/libpq.so' language 'C';
   >
   >  create trigger trial before insert or update on db1user for each row
   > execute procedure  insert_into_db2();
   >
   >  ERROR:  CreateTrigger: function insert_into_db2() must return OPAQUE
   >
   > Does anybody know why?
   >
   > Also, if a function is enforced to return OPAQUE then  what is the
   > "RETURNS" clause?
   >
   >
   > One more question,
   >      If a trigger does not succeed then I want to ABORT transaction, is
   > it possible? how?
   >
   >
   > - Sandeep
   
   -- 
   -------- Robert B. Easter  reaster@comptechnews.com ---------
   - CompTechNews Message Board   http://www.comptechnews.com/ -
   - CompTechServ Tech Services   http://www.comptechserv.com/ -
   ---------- http://www.comptechnews.com/~reaster/ ------------
   
   

3. Re: create trigger

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-15T00:08:20Z

   Robert,
       Thanks. If you have any simple  PL/pgSQL trigger that will also help.
   Indirectly.
   
   Sandeep
   
   
   > Trigger functions, no matter what language, have be RETURNS OPAQUE.
   >
   > Here is an example (in the PostgreSQL docs):
   > http://www.comptechnews.com/~reaster/postgres/triggers20290.htm
   >
   > Sorry I can't help more as I've never actually used C triggers, just PL/pgSQL
   > ones.
   >
   > On Thursday 14 December 2000 18:04, Sandeep Joshi wrote:
   > > I am trying to use "triggers". I get an error after "create trigger".
   > > Does anybody know why?
   > >
   > >  create function insert_into_db2 RETURNS int AS
   > > '/work/posgresql/lib/libpq.so' language 'C';
   > >
   > >  create trigger trial before insert or update on db1user for each row
   > > execute procedure  insert_into_db2();
   > >
   > >  ERROR:  CreateTrigger: function insert_into_db2() must return OPAQUE
   > >
   > > Does anybody know why?
   > >
   > > Also, if a function is enforced to return OPAQUE then  what is the
   > > "RETURNS" clause?
   > >
   > >
   > > One more question,
   > >      If a trigger does not succeed then I want to ABORT transaction, is
   > > it possible? how?
   > >
   > >
   > > - Sandeep
   >
   > --
   > -------- Robert B. Easter  reaster@comptechnews.com ---------
   > - CompTechNews Message Board   http://www.comptechnews.com/ -
   > - CompTechServ Tech Services   http://www.comptechserv.com/ -
   > ---------- http://www.comptechnews.com/~reaster/ ------------
   
   

4. Re: create trigger (can't compile example, problem with include files)

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-15T00:27:45Z

   Robert,
         I am insert_username.* in contrib directory for reference. So far so good.
   
   Sandeep
   "Robert B. Easter" wrote:
   
   > On Thursday 14 December 2000 18:54, Robert B. Easter wrote:
   > > Trigger functions, no matter what language, have be RETURNS OPAQUE.
   > >
   > > Here is an example (in the PostgreSQL docs):
   > > http://www.comptechnews.com/~reaster/postgres/triggers20290.htm
   > >
   > > Sorry I can't help more as I've never actually used C triggers, just
   > > PL/pgSQL ones.
   > >
   >
   > Admitting I have not tried using C triggers, I decided to try the example at
   > the url above.  I saved the example C trigger to a file called ttest.c and
   > compile it with:
   >
   > gcc -shared -I/usr/local/pgsql/include ttest.c -o ttest.o
   >
   > (I hope that is the right command), then I got this:
   >
   > reaster@comptechnews:~/prog/triggers$ gcc -shared -I/usr/local/pgsql/include
   > ttest.c -o ttest.o
   > In file included from ttest.c:1:
   > /usr/local/pgsql/include/executor/spi.h:17: nodes/primnodes.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:18: nodes/relation.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:19: nodes/execnodes.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:20: nodes/plannodes.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:21: catalog/pg_proc.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:22: catalog/pg_type.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:23: tcop/pquery.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:24: tcop/tcopprot.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:25: tcop/utility.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:26: tcop/dest.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:27: nodes/params.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:28: utils/fcache.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:29: utils/datum.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:30: utils/syscache.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:31: utils/portal.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:32: utils/builtins.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:33: catalog/pg_language.h: No such
   > file or directory
   > /usr/local/pgsql/include/executor/spi.h:34: access/heapam.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:35: access/xact.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:36: executor/executor.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:37: executor/execdefs.h: No such file
   > or directory
   > In file included from ttest.c:2:
   > /usr/local/pgsql/include/commands/trigger.h:16: nodes/execnodes.h: No such
   > file or directory
   > /usr/local/pgsql/include/commands/trigger.h:17: nodes/parsenodes.h: No such
   > file or directory
   > reaster@comptechnews:~/prog/triggers$
   >
   > I compared /usr/local/pgsql/include with
   > /usr/src/postgresql-7.0.3/src/include and found that the src has more include
   > files so that I tried:
   >
   > reaster@comptechnews:~/prog/triggers$ gcc -shared -I/usr/local/pgsql/include
   > -I/usr/src/postgresql-7.0.3/src/include ttest.c -o ttest.o
   > ttest.c: In function `trigf':
   > ttest.c:17: `WARN' undeclared (first use in this function)
   > ttest.c:17: (Each undeclared identifier is reported only once
   > ttest.c:17: for each function it appears in.)
   >
   >
   > Anyhow, can someone help me here with these include files and the missing
   > WARN define?
   >
   > > On Thursday 14 December 2000 18:04, Sandeep Joshi wrote:
   > > > I am trying to use "triggers". I get an error after "create trigger".
   > > > Does anybody know why?
   > > >
   > > >  create function insert_into_db2 RETURNS int AS
   > > > '/work/posgresql/lib/libpq.so' language 'C';
   > > >
   > > >  create trigger trial before insert or update on db1user for each row
   > > > execute procedure  insert_into_db2();
   > > >
   > > >  ERROR:  CreateTrigger: function insert_into_db2() must return OPAQUE
   > > >
   > > > Does anybody know why?
   > > >
   > > > Also, if a function is enforced to return OPAQUE then  what is the
   > > > "RETURNS" clause?
   > > >
   > > >
   > > > One more question,
   > > >      If a trigger does not succeed then I want to ABORT transaction, is
   > > > it possible? how?
   > > >
   > > >
   > > > - Sandeep
   >
   > --
   > -------- Robert B. Easter  reaster@comptechnews.com ---------
   > - CompTechNews Message Board   http://www.comptechnews.com/ -
   > - CompTechServ Tech Services   http://www.comptechserv.com/ -
   > ---------- http://www.comptechnews.com/~reaster/ ------------
   
   > On Thursday 14 December 2000 18:54, Robert B. Easter wrote:
   > > Trigger functions, no matter what language, have be RETURNS OPAQUE.
   > >
   > > Here is an example (in the PostgreSQL docs):
   > > http://www.comptechnews.com/~reaster/postgres/triggers20290.htm
   > >
   > > Sorry I can't help more as I've never actually used C triggers, just
   > > PL/pgSQL ones.
   > >
   >
   > Admitting I have not tried using C triggers, I decided to try the example at
   > the url above.  I saved the example C trigger to a file called ttest.c and
   > compile it with:
   >
   > gcc -shared -I/usr/local/pgsql/include ttest.c -o ttest.o
   >
   > (I hope that is the right command), then I got this:
   >
   > reaster@comptechnews:~/prog/triggers$ gcc -shared -I/usr/local/pgsql/include
   > ttest.c -o ttest.o
   > In file included from ttest.c:1:
   > /usr/local/pgsql/include/executor/spi.h:17: nodes/primnodes.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:18: nodes/relation.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:19: nodes/execnodes.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:20: nodes/plannodes.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:21: catalog/pg_proc.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:22: catalog/pg_type.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:23: tcop/pquery.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:24: tcop/tcopprot.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:25: tcop/utility.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:26: tcop/dest.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:27: nodes/params.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:28: utils/fcache.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:29: utils/datum.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:30: utils/syscache.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:31: utils/portal.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:32: utils/builtins.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:33: catalog/pg_language.h: No such
   > file or directory
   > /usr/local/pgsql/include/executor/spi.h:34: access/heapam.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:35: access/xact.h: No such file or
   > directory
   > /usr/local/pgsql/include/executor/spi.h:36: executor/executor.h: No such file
   > or directory
   > /usr/local/pgsql/include/executor/spi.h:37: executor/execdefs.h: No such file
   > or directory
   > In file included from ttest.c:2:
   > /usr/local/pgsql/include/commands/trigger.h:16: nodes/execnodes.h: No such
   > file or directory
   > /usr/local/pgsql/include/commands/trigger.h:17: nodes/parsenodes.h: No such
   > file or directory
   > reaster@comptechnews:~/prog/triggers$
   >
   > I compared /usr/local/pgsql/include with
   > /usr/src/postgresql-7.0.3/src/include and found that the src has more include
   > files so that I tried:
   >
   > reaster@comptechnews:~/prog/triggers$ gcc -shared -I/usr/local/pgsql/include
   > -I/usr/src/postgresql-7.0.3/src/include ttest.c -o ttest.o
   > ttest.c: In function `trigf':
   > ttest.c:17: `WARN' undeclared (first use in this function)
   > ttest.c:17: (Each undeclared identifier is reported only once
   > ttest.c:17: for each function it appears in.)
   >
   >
   > Anyhow, can someone help me here with these include files and the missing
   > WARN define?
   >
   > > On Thursday 14 December 2000 18:04, Sandeep Joshi wrote:
   > > > I am trying to use "triggers". I get an error after "create trigger".
   > > > Does anybody know why?
   > > >
   > > >  create function insert_into_db2 RETURNS int AS
   > > > '/work/posgresql/lib/libpq.so' language 'C';
   > > >
   > > >  create trigger trial before insert or update on db1user for each row
   > > > execute procedure  insert_into_db2();
   > > >
   > > >  ERROR:  CreateTrigger: function insert_into_db2() must return OPAQUE
   > > >
   > > > Does anybody know why?
   > > >
   > > > Also, if a function is enforced to return OPAQUE then  what is the
   > > > "RETURNS" clause?
   > > >
   > > >
   > > > One more question,
   > > >      If a trigger does not succeed then I want to ABORT transaction, is
   > > > it possible? how?
   > > >
   > > >
   > > > - Sandeep
   >
   > --
   > -------- Robert B. Easter  reaster@comptechnews.com ---------
   > - CompTechNews Message Board   http://www.comptechnews.com/ -
   > - CompTechServ Tech Services   http://www.comptechserv.com/ -
   > ---------- http://www.comptechnews.com/~reaster/ ------------
   
   

5. Re: create trigger (can't compile example, problem with include files)

   Robert B. Easter <reaster@comptechnews.com> — 2000-12-15T00:29:41Z

   On Thursday 14 December 2000 18:54, Robert B. Easter wrote:
   > Trigger functions, no matter what language, have be RETURNS OPAQUE.
   >
   > Here is an example (in the PostgreSQL docs):
   > http://www.comptechnews.com/~reaster/postgres/triggers20290.htm
   >
   > Sorry I can't help more as I've never actually used C triggers, just
   > PL/pgSQL ones.
   >
   
   Admitting I have not tried using C triggers, I decided to try the example at 
   the url above.  I saved the example C trigger to a file called ttest.c and 
   compile it with:
   
   gcc -shared -I/usr/local/pgsql/include ttest.c -o ttest.o
   
   (I hope that is the right command), then I got this:
   
   reaster@comptechnews:~/prog/triggers$ gcc -shared -I/usr/local/pgsql/include 
   ttest.c -o ttest.o
   In file included from ttest.c:1:
   /usr/local/pgsql/include/executor/spi.h:17: nodes/primnodes.h: No such file 
   or directory
   /usr/local/pgsql/include/executor/spi.h:18: nodes/relation.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:19: nodes/execnodes.h: No such file 
   or directory
   /usr/local/pgsql/include/executor/spi.h:20: nodes/plannodes.h: No such file 
   or directory
   /usr/local/pgsql/include/executor/spi.h:21: catalog/pg_proc.h: No such file 
   or directory
   /usr/local/pgsql/include/executor/spi.h:22: catalog/pg_type.h: No such file 
   or directory
   /usr/local/pgsql/include/executor/spi.h:23: tcop/pquery.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:24: tcop/tcopprot.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:25: tcop/utility.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:26: tcop/dest.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:27: nodes/params.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:28: utils/fcache.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:29: utils/datum.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:30: utils/syscache.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:31: utils/portal.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:32: utils/builtins.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:33: catalog/pg_language.h: No such 
   file or directory
   /usr/local/pgsql/include/executor/spi.h:34: access/heapam.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:35: access/xact.h: No such file or 
   directory
   /usr/local/pgsql/include/executor/spi.h:36: executor/executor.h: No such file 
   or directory
   /usr/local/pgsql/include/executor/spi.h:37: executor/execdefs.h: No such file 
   or directory
   In file included from ttest.c:2:
   /usr/local/pgsql/include/commands/trigger.h:16: nodes/execnodes.h: No such 
   file or directory
   /usr/local/pgsql/include/commands/trigger.h:17: nodes/parsenodes.h: No such 
   file or directory
   reaster@comptechnews:~/prog/triggers$                                         
   
   
   I compared /usr/local/pgsql/include with
   /usr/src/postgresql-7.0.3/src/include and found that the src has more include 
   files so that I tried:
   
   reaster@comptechnews:~/prog/triggers$ gcc -shared -I/usr/local/pgsql/include 
   -I/usr/src/postgresql-7.0.3/src/include ttest.c -o ttest.o
   ttest.c: In function `trigf':
   ttest.c:17: `WARN' undeclared (first use in this function)
   ttest.c:17: (Each undeclared identifier is reported only once
   ttest.c:17: for each function it appears in.)                                 
                                                                
   
   Anyhow, can someone help me here with these include files and the missing 
   WARN define?
   
   
   > On Thursday 14 December 2000 18:04, Sandeep Joshi wrote:
   > > I am trying to use "triggers". I get an error after "create trigger".
   > > Does anybody know why?
   > >
   > >  create function insert_into_db2 RETURNS int AS
   > > '/work/posgresql/lib/libpq.so' language 'C';
   > >
   > >  create trigger trial before insert or update on db1user for each row
   > > execute procedure  insert_into_db2();
   > >
   > >  ERROR:  CreateTrigger: function insert_into_db2() must return OPAQUE
   > >
   > > Does anybody know why?
   > >
   > > Also, if a function is enforced to return OPAQUE then  what is the
   > > "RETURNS" clause?
   > >
   > >
   > > One more question,
   > >      If a trigger does not succeed then I want to ABORT transaction, is
   > > it possible? how?
   > >
   > >
   > > - Sandeep
   
   -- 
   -------- Robert B. Easter  reaster@comptechnews.com ---------
   - CompTechNews Message Board   http://www.comptechnews.com/ -
   - CompTechServ Tech Services   http://www.comptechserv.com/ -
   ---------- http://www.comptechnews.com/~reaster/ ------------
   
   

6. Re: create trigger (can't compile example, problem with include files)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-15T03:51:38Z

   "Robert B. Easter" <reaster@comptechnews.com> writes:
   > I compared /usr/local/pgsql/include with
   > /usr/src/postgresql-7.0.3/src/include and found that the src has more
   > include files so that I tried:
   
   Yeah, we keep meaning to clean up the include-file situation so that you
   have some chance of compiling a useful C trigger with the installed
   include files.  Right now you pretty much have to -I a source tree.
   
   > ttest.c: In function `trigf':
   > ttest.c:17: `WARN' undeclared (first use in this function)
   > ttest.c:17: (Each undeclared identifier is reported only once
   > ttest.c:17: for each function it appears in.)
   
   s/WARN/ERROR/.   Looks like this example file never got updated when
   that change was made, about three years ago :-(.  Will fix.
   
   			regards, tom lane