Обсуждение: trigger errors

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 ";"

definitions follow(this is the exact syntax and order i used to create the
function table and trigger, all other tables in referenced do exist and have
ALL permissions turned on for the user)

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;
     COMMIT;
       '
       LANGUAGE 'plpgsql';


CREATE TABLE snippet (
       UserName         VARCHAR(12),
       LangID         INT,
       CatID         INT DEFAULT 0,
       name         VARCHAR(30),
       snippet         TEXT);
grant ALL ON snippet to codelibrary;

CREATE INDEX front_select ON snippet (UserName, LangID, name);
CREATE INDEX cat_select ON snippet (UserName, CatID, name);

CREATE TRIGGER count_change
       AFTER DELETE OR INSERT ON snippet FOR EACH ROW
       EXECUTE PROCEDURE create_count_cache();

i then call

insert into snippet (UserName, LangID, name, snippet)
 values ('mbritten', 1, 'my snippet', 'int i = 0;');

and get the error.

thanks for your time,

marc britten

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