ERROR: column "int4" specified more than once

Question

Steps for Execution:

Table Creation

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

Add some dummy data :

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

Function Creation

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$:

Call Function

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

Once you run these steps, I am getting an error

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

user_939860 · Answer

Look at the error message closely:

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

There are multiple expressions without column alias. A named column like "id" defaults to the given name. But other expressions default to the internal type name, which is "int4" for integer. One might assume that the JSON key name is used, but that's not so. CAST(scores->>'a' AS INTEGER) is just another expression returning an unnamed integer value.

This still works for a plain SELECT. Postgres tolerates duplicate column names in the (outer) SELECT list. But a VIEW cannot be created that way. Would result in ambiguities.

Either add column aliases to expressions in the SELECT list:

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

Or add a list of column names to CREATE VIEW:

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

Something like this should fix your function (preserving literal spelling of JSON key names:

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

See the working demo here:

db<>fiddle here

Aside, your nested format() calls make the code pretty hard to read and maintain.

ERROR: column "int4" specified more than once

1 Answers