无法创建函数：“*”处或附近有语法错误

发布于 2025-01-10 02:36:39 字数 350 浏览 3 评论 0原文

我正在尝试使用以下代码在 Supabase 中创建一个触发器，这将帮助我更新另一个表中的某个值。以下是supabase函数的触发代码

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;

但是，我收到以下错误无法创建函数：“*”处或附近的语法错误

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三寸金莲 2025-01-17 02:36:39

有多个错误正如您在手册中看到的

DECLARE 部分位于 BEGIN 之前。

正如手册中所述，INSERT 语句没有 WHERE 子句。

因此，假设您正确地掌握了函数（或过程）的其余部分（您没有向我们展示，那么 PL/pgSQL 块需要如下所示：

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;

我不清楚您对 INSERT 语句的意图是什么。如果您是尝试更改现有行，您需要一个UPDATE语句：

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

请注意，如果触发器定义为在series表上触发，那么您不需要UPDATE完全做到了。 BEFORE 触发器并简单地分配新计数：

new.chapcount := num;
return new;

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: