Thread: create trigger

create trigger

From

Sandeep Joshi

Date:

14 December 2000, 21:04:19

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

Re: create trigger

From

"Robert B. Easter"

Date:

14 December 2000, 21:47:31

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/ ------------

Re: create trigger

From

Sandeep Joshi

Date:

14 December 2000, 22:08:03

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/ ------------

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

From

"Robert B. Easter"

Date:

14 December 2000, 22:22:30

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/ ------------

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

From

Sandeep Joshi

Date:

14 December 2000, 22:27:23

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/ ------------

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

From

Tom Lane

Date:

15 December 2000, 01:56:24

"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