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(at)yellowbank(dot)com