使用动态强制转换从表创建 postgres 视图

Question

我想创建一个通用查询，该查询将允许我创建一个视图(从表中(并将所有数组列转换为字符串。

像这样：

CREATE OR REPLACE VIEW view_1 AS
SELECT *
for each column_name in columns
CASE WHEN pg_typeof(column_name) == TEXT[] THEN array_to_string(column_name)
ELSE column_name
FROM table_1;

我想我可以用stored procedure做到这一点，但我正在寻找纯SQL的解决方案，如果它可以非常复杂的话。

Answer 1

下面是执行此类转换的查询。然后，您可以自定义它以创建视图并执行它。

SELECT
'CREATE OR REPLACE VIEW my_table_view AS SELECT ' ||  string_agg( 
CASE 
WHEN pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) LIKE '%[]' THEN 'array_to_string(' || pg_attribute.attname || ', '','') AS ' || pg_attribute.attname
ELSE pg_attribute.attname
END, ', ' ORDER BY attnum ASC)
|| ' FROM ' || min(pg_class.relname) || ';'
FROM
pg_catalog.pg_attribute
INNER JOIN
pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
pg_attribute.attnum > 0
AND NOT pg_attribute.attisdropped
AND pg_namespace.nspname = 'my_schema'
AND pg_class.relname = 'my_table'
; gexec

例：

create table tarr (id integer, t_arr1 text[], regtext text, t_arr2 text[], int_arr integer[]);

==>

SELECT id, array_to_string(t_arr1) AS t_arr1, regtext, array_to_string(t_arr2) AS t_arr2, int_arr FROM tarr;