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.pgsql.general.960922068.691@illiad.adhesivemedia.com>,
> Marc Britten  <mbritten@cybernet-usa.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@Yahoo.com #