Thread: trigger troubles

Why does this not work? It's a plpython function if that changes
anything

perversity=# \df for*
                            List of functions
 Result data type |   Schema   |       Name        | Argument data types
------------------+------------+-------------------+---------------------
 text             | pg_catalog | format_type       | oid, integer
 "trigger"        | public     | foreign_key_check | text
(2 rows)

perversity=# create trigger f_foreign_key_check before insert or update
on f for each row execute procedure foreign_key_check ('f');
ERROR:  CreateTrigger: function foreign_key_check() does not exist
perversity=#

James.

On 20 Mar 2003, James Gregory wrote:

> Why does this not work? It's a plpython function if that changes
> anything
>
> perversity=# \df for*
>                             List of functions
>  Result data type |   Schema   |       Name        | Argument data types
> ------------------+------------+-------------------+---------------------
>  text             | pg_catalog | format_type       | oid, integer
>  "trigger"        | public     | foreign_key_check | text
> (2 rows)
>
> perversity=# create trigger f_foreign_key_check before insert or update
> on f for each row execute procedure foreign_key_check ('f');
> ERROR:  CreateTrigger: function foreign_key_check() does not exist

Trigger functions should be created to return trigger and take no
arguments.  The arguments from create trigger are generally passed in
a different way (although I don't know what that is for plpython)

On Thu, 2003-03-20 at 04:10, Stephan Szabo wrote:
> On 20 Mar 2003, James Gregory wrote:
>
> > Why does this not work? It's a plpython function if that changes
> > anything
> >
> > perversity=# \df for*
> >                             List of functions
> >  Result data type |   Schema   |       Name        | Argument data types
> > ------------------+------------+-------------------+---------------------
> >  text             | pg_catalog | format_type       | oid, integer
> >  "trigger"        | public     | foreign_key_check | text
> > (2 rows)
> >
> > perversity=# create trigger f_foreign_key_check before insert or update
> > on f for each row execute procedure foreign_key_check ('f');
> > ERROR:  CreateTrigger: function foreign_key_check() does not exist
>
> Trigger functions should be created to return trigger and take no
> arguments.  The arguments from create trigger are generally passed in
> a different way (although I don't know what that is for plpython)

It would be sufficient to get the table name from within the python code
somehow, but I couldn't find a way to do this.

From

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpython-trigger.html

and

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createtrigger.html

it looks very much like I am using the correct syntax. Is this a bug? Is
there a work around? Currently the only thing I can think of is to
encode the table name in the trigger name, since plpython claims to be
able to get at that.

Does this sort of thing work in CVS?

James.

On 20 Mar 2003, James Gregory wrote:

> On Thu, 2003-03-20 at 04:10, Stephan Szabo wrote:
> > On 20 Mar 2003, James Gregory wrote:
> >
> > >                             List of functions
> > >  Result data type |   Schema   |       Name        | Argument data types
> > > ------------------+------------+-------------------+---------------------
> > >  text             | pg_catalog | format_type       | oid, integer
> > >  "trigger"        | public     | foreign_key_check | text
> > > (2 rows)
> > >
> > > perversity=# create trigger f_foreign_key_check before insert or update
> > > on f for each row execute procedure foreign_key_check ('f');
> > > ERROR:  CreateTrigger: function foreign_key_check() does not exist
> >
> > Trigger functions should be created to return trigger and take no
> > arguments.  The arguments from create trigger are generally passed in
> > a different way (although I don't know what that is for plpython)
>
> It would be sufficient to get the table name from within the python code
> somehow, but I couldn't find a way to do this.
>
> From
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpython-trigger.html
> and
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createtrigger.html
>
> it looks very much like I am using the correct syntax. Is this a bug? Is

Your function is defined to take an argument of type text, but trigger
functions must be defined to take no arguments. So you need to make the
function as foreign_key_check() [no args] and it looks like you use
TD["args"][0] to get the argument you're passing at create trigger time.