Re: triggers and functions in pgsql 7.0.2

Philip Hallstrom wrote:
> I had this same problem... plpgsql is not built be default (at least for
> 7.0). take a look at src/pl/plpgsql/src/INSTALL. You just need to add
> it... then switch your language back to plpgsql and it should work
> (well, maybe it won't, but it shouldn't complain about not finding
> plpgsql)
> good luck!
> -philip
> In article <am(dot)pgsql(dot)general(dot)960922068(dot)691(at)illiad(dot)adhesivemedia(dot)com>,
> Marc Britten <mbritten(at)cybernet-usa(dot)com> wrote:
> >I'm going through your docs trying to create a function and a trigger that
> >calls said function, what I've gotten so far is
> >CREATE FUNCTION create_count_cache()
> > RETURNS opaque AS
> > '
> > BEGIN;
> > DELETE FROM SnipCount;
> > INSERT INTO SnipCount SELECT LangID, COUNT(LangID) AS CNT FROM
> >snippet GROUP BY LangID;
> > COMMIT;
> > '
> > LANGUAGE 'sql';

Yepp, won't work.

CREATE FUNCTION create_count_cache() RETURNS opaque AS '
BEGIN
DELETE FROM SnipCount;
INSERT ...
RETURN NEW;
END;'
LANGUAGE 'plpgsql';

In PL/pgSQL, BEGIN isn't the begin of a separate transaction.
It's a keyword for putting groups of statements into separate
variable visibility. A little confusing, but so far
compatible to Oracle's PL/SQL.

And BTW: This trigger will purge out and repopulate the
entire SnipCount relation for each single row touched in
snippet. If that's what you want, it's OK.

But I bet you want to make it smarter and only
create/update/delete snipcount records that are touched by
the operation - don't you?

> >CREATE TRIGGER count_change
> > AFTER DELETE OR INSERT ON snippet FOR EACH ROW
> > EXECUTE PROCEDURE create_count_cache();
> >however LANGUAGE sql cannot return opaque and plpgsql is not a known lang
> >type.
> >it seems that all your docs are a bit out of date, can you give me some help
> >please?
> >marc britten
>

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #