[Cialug] Postgres trigger help.

[Tim Wilson]

On 8/13/07, Josh More <morej at alliancetechnologies.net> wrote:
>
> Are you defining your functions before you call them as triggers?  I
> remember having that issue when I did this years ago.



I tried creating the trigger first, but it said the function didn't exist.

Josh suggested these 2 commands off list:

create function plpgsql_call_handler () returns opaque as
'/usr/lib/pgsql/plpgsql.so' language 'C';
create trusted language 'plpgsql' handler plpgsql_call_handler lancompiler
'PL/pgSQL';

After executing those 2 commands, I was able to create the function and the
trigger.  But when I update the database, the last_updated field isn't set.
Here's what I have:

CREATE FUNCTION set_last_update() RETURNS opaque AS '
BEGIN
NEW.last_updated:=now();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER set_last_update AFTER INSERT OR UPDATE ON people FOR EACH ROW
EXECUTE PROCEDURE set_last_update();

I've tried changing now() to ''now'' (2 single quotes before and after) like
it shows in the postgres documentation online, but that doesn't work.  I
tried changing it to double quotes, and I got an unterminated quote error.

I'm sure there's something simple I'm missing.




-Josh More, RHCE, CISSP, NCLP, GIAC
> morej at alliancetechnologies.net
> 515-245-7701
>
> >>> "Tim Wilson" <tim_linux at wilson-home.com> 08/13/07 11:01 PM >>>
> That's one of the examples I was talking about.  The function that is
> created in the example returns type "trigger" (quotes added for
> emphasis).
> When I try that in my version, it says 'parse error at or near
> "trigger"'.
> Another example said to use void, another said opaque.  All caused the
> parser to complain.
>
> On 8/13/07, Kevin C. Smith <kevin at linuxsmith.com> wrote:
> >
> > On Mon, 2007-08-13 at 22:02 -0500, Tim Wilson wrote:
> > > I'm using Postgres 7.1.3, and I'm trying to set up a trigger.  I
> have
> > > a last_updated column on all of my tables, that is of type
> timestamp.
> > > I want to set the field every time the record is updated.  I thought
> a
> > > trigger would be best for that, but every example I find via Google
> > > doesn't work.  One said to create a function that returned type
> > > TRIGGER, another said type VOID, neither of which worked.  Every
> > > example I find uses one of those return types.  Anyone have any
> > > ideas?
> >
> > I haven't tried triggers with postgresql, but I think it's a two
> > step thing in most cases. Write a function, then create a trigger
> > to call that function on insert or update.
> >
> > Try:
> > http://www.postgresql.org/docs/8.2/static/triggers.html
> > http://www.postgresql.org/docs/8.2/static/plpgsql.html
> >
> > Religion is regarded by the common people as true, by the wise as
> false,
> > and by the rulers as useful. --- Lucius Annaeus Seneca
> >
> > _______________________________________________
> > Cialug mailing list
> > Cialug at cialug.org
> > http://cialug.org/mailman/listinfo/cialug
> >
>
>
>
> --
> Tim
>
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug
>



-- 
Tim
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cialug.org/pipermail/cialug/attachments/20070814/db388986/attachment-0001.html