Re: trigger errors

Marc Britten wrote:
>
> hi again,
>
> i created a trigger on a table for insert or delete, and a function that
> gets created fine.
>
> however when i go to insert something into the table i get
>
> NOTICE:  plpgsql: ERROR during compile of create_count_cache near line 2
> ERROR:  parse error at or near ";"

Hi Marc,

Try this function instead:

CREATE FUNCTION create_count_cache()
RETURNS opaque AS '
    BEGIN
        DELETE FROM LangCount;
        INSERT INTO LangCount
            SELECT LangID as ID, COUNT(LangID) AS CNT
            FROM snippet
            GROUP BY LangID;
        DELETE FROM CatCount;
        INSERT INTO CatCount
            SELECT LangID as ID, COUNT(LangID) AS CNT
            FROM snippet
            GROUP BY LangID;
        RETURN NEW;
    END;'
LANGUAGE 'plpgsql';

There are two changes from what you have.  You cannot use BEGIN/COMMIT
(i.e. transactions) within a function or trigger.  In plpgsql, use BEGIN
and END to delimit your function statements.  There is more information
about this in the PL/pgSQL portion of the user's guide.  The function
also needs to return something.  A return type of 'opaque' doesn't mean
the function returns nothing, it means the function doesn't return a
defined SQL datatype.

________________________
Ron Peterson
rpeterson@yellowbank.com