postgresql - 错误 : column "int4" specified more than once

Question

执行步骤:

表创建

CREATE TABLE xyz.table_a(
   id bigint NOT NULL,
   scores jsonb,
   CONSTRAINT table_a_pkey PRIMARY KEY (id)
);

添加一些虚拟数据:

INSERT INTO xyz.table_a(
id, scores)
VALUES (1, '{"a":20,"b":20}');

函数创建

CREATE OR REPLACE FUNCTION xyz.example(
   table_name text,
   regular_columns text,
   json_column text,
   view_name text
) RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
   cols TEXT;
   cols_sum TEXT;
BEGIN
   EXECUTE
      format(
         $ex$SELECT string_agg(
                       format(
                          'CAST(%2$s->>%%1$L AS INTEGER)',
                          key),
                       ', '
                    )
             FROM (SELECT DISTINCT key
                   FROM %1$s, jsonb_each(%2$s)
                   ORDER BY 1
                  ) s;$ex$,
         table_name, json_column
      )
   INTO cols;

   EXECUTE
      format(
         $ex$SELECT string_agg(
                       format(
                          'CAST(%2$s->>%%1$L AS INTEGER)',
                          key
                       ),
                       '+'
                    )
             FROM (SELECT DISTINCT key
                   FROM %1$s, jsonb_each(%2$s)
                   ORDER BY 1) s;$ex$,
         table_name, json_column
      )
   INTO cols_sum;

   EXECUTE
      format(
         $ex$DROP VIEW IF EXISTS %2$s;
             CREATE VIEW %2$s AS
             SELECT %3$s, %4$s, SUM(%5$s) AS total
             FROM %1$s
             GROUP BY %3$s$ex$,
         table_name, view_name, regular_columns, cols, cols_sum
      );

   RETURN cols;
END
$BODY$:

调用函数

SELECT xyz.example(
   'xyz.table_a',
   ' id',
   'scores',
   'xyz.view_table_a'
);

运行这些步骤后，我收到一个错误

ERROR:  column "int4" specified more than once
CONTEXT:  SQL statement "
DROP VIEW IF EXISTS xyz.view_table_a;
CREATE VIEW xyz.view_table_a AS
SELECT  id, CAST(scores->>'a' AS INTEGER), CAST(scores->>'b' AS INTEGER), SUM(CAST(scores->>'a' AS INTEGER)+CAST(scores->>'b' AS INTEGER)) AS total FROM xyz.table_a GROUP BY  id

Answer 1

仔细查看错误信息:

...
SELECT  id, CAST(scores->>'a' AS INTEGER), CAST(scores->>'b' AS INTEGER),
...

有多个没有列别名的表达式。像“id”这样的命名列默认为给定的名称。但其他表达式默认为内部类型名称，即 integer 的“int4” .人们可能会假设使用了 JSON key 名称，但事实并非如此。 CAST(scores->>'a' AS INTEGER)只是另一个返回未命名整数值的表达式。
这仍然适用于普通 SELECT . Postgres 允许(外部) SELECT 中的列名重复列表。但是一个 VIEW 不能以这种方式创建。会造成歧义。
向 SELECT 中的表达式添加列别名列表:

SELECT id, CAST(scores->>'a' AS INTEGER) AS a, CAST(scores->>'b' AS INTEGER) AS b, ...

或者将列名列表添加到 CREATE VIEW :

CREATE VIEW xyz.view_table_a(id, a, b, ...) AS ...

这样的事情应该可以修复您的功能(保留 JSON 键名称的字面拼写:

...
                       format(
                          'CAST(%2$s->>%%1$L AS INTEGER) AS %%1$I',
                          key),
...

在此处查看工作演示:
分贝<> fiddle here
另外，你嵌套的 format()调用使代码很难阅读和维护。