时间戳

首页 > 解决方案 > Postgresql - 错误：“RETURN”SQL 状态或附近的语法错误：42601

sql - Postgresql - 错误：“RETURN”SQL 状态或附近的语法错误：42601

问题描述

我正在使用 Postgresql 9.6 并尝试创建以下函数：

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS json
    LANGUAGE 'sql'
    
    
AS $BODY$

CREATE TEMP TABLE t1 AS 
          SELECT * FROM unnest(vals1Arg) AS u(c);

CREATE TEMP TABLE t2 AS 
          SELECT * FROM unnest(vals2Arg) AS u(c);

RETURN QUERY 
    SELECT * FROM "INVENTORY"
        JOIN t1 ON "COLUMN_1" = t1.c
        JOIN t2 ON "COLUMN_2" = t2.c;

$BODY$;

我的实际要求在单独的帖子中提到

我收到以下语法错误：

ERROR:  syntax error at or near "RETURN"
LINE 12: RETURN QUERY 
         ^
SQL state: 42601

我该如何解决？

提前致谢！

标签： sqlpostgresqlpostgresql-9.6

解决方案

您不能return query在language sql函数中使用。

当您使参数区分大小写时，您还必须在使用它们时引用它们。

但是当您从表中返回所有行时，"INVENTORY"您还需要将您的函数声明为returns setof “INVENTORY”`

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS setof "INVENTORY"
    LANGUAGE sql
AS 
$BODY$
  CREATE TEMP TABLE t1 AS 
          SELECT * FROM unnest("vals1Arg") AS u(c);

  CREATE TEMP TABLE t2 AS 
          SELECT * FROM unnest("vals2Arg") AS u(c);

  SELECT i.*
  FROM "INVENTORY" i
    JOIN t1 ON i."COLUMN_1" = t1.c
    JOIN t2 ON i."COLUMN_2" = t2.c;
$BODY$;

但是临时表是不必要的（并且会减慢查询速度）。您可以unnest直接在查询中使用：

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS setof "INVENTORY"
    LANGUAGE sql
AS 
$BODY$
  SELECT i.*
  FROM "INVENTORY" i
    JOIN unnest("vals1Arg") AS t1(c) ON i."COLUMN_1" = t1.c
    JOIN unnest("vals2Arg") AS t2(c) ON i."COLUMN_2" = t2.c;
$BODY$;

但是你真的想加入参数吗？也许您打算使用“IN”条件？

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS setof "INVENTORY"
    LANGUAGE sql
AS 
$BODY$
  SELECT i.*
  FROM "INVENTORY" i
  WHERE i."COLUMN_1" = any("vals1Arg")
    AND i."COLUMN_1" = any("vals2Arg");
$BODY$;

推荐阅读