Обсуждение: functions and triggers

I'm trying to build a trigger that will update a timestamp field in a table with
the current timestamp, and I just can't make it work.

The problemas are two:

1) when I try to create the trigger, it says that the function doesn't exist.
Why is this happening?
2) How does the trigger tell the function the row number identifier?

I'm really stuck with this.

Saludos... :-)

-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------

On Tue, 20 Aug 2002, Martin Marques wrote:

> I'm trying to build a trigger that will update a timestamp field in a table with
> the current timestamp, and I just can't make it work.
>
> The problemas are two:
>
> 1) when I try to create the trigger, it says that the function doesn't exist.
> Why is this happening?

You should probably show us what you were trying to do, but I'm going to
guess that the function doesn't have the right signature.  On
current versions, Trigger functions should return opaque and take no
arguments (any arguments given on the create trigger line are passed
in a different fashion).

> 2) How does the trigger tell the function the row number identifier?

I'm not sure what you mean by this.  Getting at the row being worked on
depends somewhat on what language you're using.

Quoting Stephan Szabo <sszabo@megazone23.bigpanda.com>:

> 
> > The problemas are two:
> >
> > 1) when I try to create the trigger, it says that the function doesn't
> exist.
> > Why is this happening?
> 
> You should probably show us what you were trying to do, but I'm going to
> guess that the function doesn't have the right signature.  On
> current versions, Trigger functions should return opaque and take no
> arguments (any arguments given on the create trigger line are passed
> in a different fashion).

I have this function which works OK.

CREATE FUNCTION ahora (integer) RETURNS integer AS '
UPDATE usuarios SET tmodif = now()
WHERE codigo = $1;
SELECT 1 as RESULT;
' LANGUAGE SQL

> > 2) How does the trigger tell the function the row number identifier?
> 
> I'm not sure what you mean by this.  Getting at the row being worked on
> depends somewhat on what language you're using.

I thought about a simple SQL that does the update.
You mean I just call the function from the trigger and thats all?


-- 
Porqué usar una base de datos relacional cualquiera,
si podés usar PostgreSQL?
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telematica                      Universidad Nacional
        del Litoral
 
-----------------------------------------------------------------

On Tue, 20 Aug 2002, Martin Marques wrote:

> Quoting Stephan Szabo <sszabo@megazone23.bigpanda.com>:
>
> >
> > > The problemas are two:
> > >
> > > 1) when I try to create the trigger, it says that the function doesn't
> > exist.
> > > Why is this happening?
> >
> > You should probably show us what you were trying to do, but I'm going to
> > guess that the function doesn't have the right signature.  On
> > current versions, Trigger functions should return opaque and take no
> > arguments (any arguments given on the create trigger line are passed
> > in a different fashion).
>
> I have this function which works OK.
>
> CREATE FUNCTION ahora (integer) RETURNS integer AS '
> UPDATE usuarios SET tmodif = now()
> WHERE codigo = $1;
> SELECT 1 as RESULT;
> ' LANGUAGE SQL

Trigger functions have no args and return opaque and I don't think you
can use sql language functions, but I'm not sure.  Something like:

create function ahora_trigger() returns opaque as '
beginNEW.tmodif := now();return NEW;
end;'
language 'plpgsql';

should work as a before update trigger.