Failed to create function: Syntax error at or near "*"-CodePudding

I am trying to create a trigger in Supabase with the following code that will help me update a certain value in another table. Following is the trigger code for the supabase function

BEGIN
  DECLARE num integer;
  SELECT count(*) into num FROM chapters
  WHERE seriesid=new.seriesid;

  INSERT INTO public.series(chapcount);
  WHERE id=new.seriesid;
  values(num);
  RETURN new;

END;

However, I get the following error Failed to create function: Syntax error at or near "*"

CodePudding user response：

There are multiple errors as you can see in the manual

The DECLARE section goes before the BEGIN.

And as documented in the manual the INSERT statement has no WHERE clause.

So assuming you have the rest of the function (or procedure) right (which you didn't show us, the PL/pgSQL block needs to look like this:

DECLARE
  num integer;
BEGIN
  SELECT count(*) 
    into num 
  FROM chapters
  WHERE seriesid = new.seriesid;

  INSERT INTO public.series(id, chapcount);
  values(new.seriesid, num);
  RETURN new;
END;

It's unclear to me what your intention with the INSERT statement is. If you are trying to change an existing row, you need an UPDATE statement:

 UPDATE public.series
    SET chapcount = num
 WHERE id = new.seriesid;

Note that if the trigger is defined to be fired on the series table, then you don't need an UPDATE at all. Make it a BEFORE trigger and simply assign the new count:

new.chapcount := num;
return new;