如何在存储过程中调用数组?

Question

我可以知道如何在存储过程中调用数组吗?我试着用括号把它括起来，把需要插入到新表中的column_name。

CREATE OR REPLACE PROCEDURE data_versioning_nonull(new_table_name VARCHAR(100),column_name VARCHAR(100)[], current_table_name VARCHAR(100))
language plpgsql
as $$
BEGIN
EXECUTE ('CREATE TABLE ' || quote_ident(new_table_name) || ' AS SELECT ' || quote_ident(column_name) || ' FROM ' || quote_ident(current_table_name));
END $$;
CALL data_versioning_nonull('sales_2019_sample', ['orderid', 'product', 'address'], 'sales_2019');

Answer 1

1. 使用execute format()可以让您用%I占位符替换单个文本中的所有quote_ident()，而不是一系列连接的片段。%1$I允许您重用第一个参数。
2. 最好使用ARRAY['a','b','c']::VARCHAR(100)[]显式地使其成为所需类型的数组。'{"a","b","c"}'::VARCHAR(100)[]也可以。
3. 您需要以其他方式将数组转换为列列表，因为当转换为文本时，它将获得列列表语法中不允许的花括号。演示
4. 引入随机限制并不是一个好的实践- PostgreSQL没有限制标识符长度为100个字符，所以你也不必这样做。默认限制是63字节，所以你可以比100个字符长得多(demo)。您可以将该数据类型切换为常规的text。有趣的是，超过指定的varchar长度只会将其转换为无限的varchar，使其只是语法噪音。

DBFiddle在线演示

CREATE TABLE sales_2019(orderid INT,product INT,address INT);
CREATE OR REPLACE PROCEDURE data_versioning_nonull(
new_table_name     TEXT,
column_names       TEXT[], 
current_table_name TEXT)
LANGUAGE plpgsql AS $$
DECLARE
list_of_columns_as_quoted_identifiers TEXT;
BEGIN
SELECT string_agg(quote_ident(name),',')
INTO list_of_columns_as_quoted_identifiers
FROM unnest(column_names) name;

EXECUTE format('CREATE TABLE %1$I.%2$I AS SELECT %3$s FROM %1$I.%4$I',
current_schema(),
new_table_name,
list_of_columns_as_quoted_identifiers,
current_table_name);
END $$;
CALL data_versioning_nonull(
'sales_2019_sample', 
ARRAY['orderid', 'product', 'address']::text[], 
'sales_2019');

5. 模式感知:目前，该过程基于相同默认模式中的表在默认模式中创建新表——上面我明确说明了这一点，但这就是它在没有current_schema()调用的情况下所做的。您可以添加new_table_schema和current_table_schema参数，如果大多数时候您不希望使用它们，您可以将它们隐藏在过程重载之后以方便使用，使用current_schema()来保持隐式行为。演示

Answer 2

首先，更改存储过程以将选定的列从数组转换为csv，如下所示。

CREATE OR REPLACE PROCEDURE data_versioning_nonull(new_table_name VARCHAR(100),column_name VARCHAR(100)[], current_table_name VARCHAR(100))
language plpgsql
as $$
BEGIN
EXECUTE ('CREATE TABLE ' || quote_ident(new_table_name) || ' AS SELECT ' || array_to_string(column_name, ',') || ' FROM ' || quote_ident(current_table_name));
END $$;

则称其为:

CALL data_versioning_nonull('sales_2019_sample', '{"orderid", "product", "address"}', 'sales_2019');