Forum OpenACS Q&A: pl sql function recommendations...

Collapse
Posted by David Kuczek on
This is a function that I just wrote with plsql but it doesn't really
work. (I am rather new to pl sql) Any recommendations how to fix it?

create function trig_categories_comments_update() returns opaque

as '

DECLARE

        v_n_comments    integer;

BEGIN

        if NEW.on_which_table = ''categories'' then

        select count(*) into v_n_comments from general_comments

        where on_what_id= NEW.on_what_id

        and on_which_table = ''categories'';

        update categories

        set n_comments= v_n_comments,

        where category_id= NEW.on_what_id;

        end if;

        return NEW;

END;

' language 'plpgsql';

create trigger categories_rating_update

after insert on general_comments

for each row

execute procedure trig_categories_comments_update();

I connected the general_comments module with the categories module.

Now I want to update categories (where I added a "n_comments integer")
with the amount of comments that are written "on_which_table =
categories" and "on_what_id = category_id" as soon as somebody adds a
comment...

The error that is being presented is  --- parse error at or near
"where"

Thanks

Collapse
Posted by David Kuczek on
sorry,

I don't know how the comma in "set n_comments= v_n_comments, " got in there. That was the mistake...