May I know on how to call an array in stored procedure? I tried to enclosed it with a bracket to put the column_name that need to be insert in the new table.

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');

CodePudding user response：

1. Using execute format() lets you replace all the quote_ident() and concatenation.
2. It's best if you use ARRAY['a','b','c']::VARCHAR(100)[] to explicitly make it an array of your desired type. '{"a","b","c"}'::VARCHAR(100)[] works too.
3. You'll need to convert the array into a list of columns some other way, because when cast to text, it'll get curly braces which are not allowed in the column list syntax. Demo
4. It's not a good practice to introduce random limitations - PostgreSQL doesn't limit identifier lengths to 100 characters, so you don't have to either. The default limit is 63 bytes, so you can go way, way longer than 100 characters (demo). You can switch that data type to a regular text.

DBFiddle online demo

CREATE TABLE sales_2019(orderid INT,product INT,address INT);

CREATE OR REPLACE PROCEDURE data_versioning_nonull(
  new_table_name     TEXT,
  column_name        TEXT[], 
  current_table_name TEXT)
LANGUAGE plpgsql AS $$
DECLARE
  list_of_columns_as_quoted_identifiers TEXT;
BEGIN
  SELECT string_agg(quote_ident_columns,',')
  INTO list_of_columns_as_quoted_identifiers
  FROM 
   ( SELECT quote_ident(
              unnest(column_name) ) AS quote_ident_columns) a;
  
  EXECUTE format('CREATE TABLE %I.%I AS SELECT %s FROM %I.%I',
                   current_schema(),
                   new_table_name,
                   list_of_columns_as_quoted_identifiers,
                   current_schema(),
                   current_table_name);
END $$;

CALL data_versioning_nonull(
  'sales_2019_sample', 
  ARRAY['orderid', 'product', 'address']::text[], 
  'sales_2019');

5. Schema awareness: currently the procedure creates the new table in the default schema, based on a table in that same default schema - above I made it explicit, but that's what it would do without the current_schema() calls anyway. You could add new_table_schema and current_table_schema parameters and if most of the time you don't expect them to be used, you can hide them behind procedure overloads for convenience, using current_schema() to keep the implicit behaviour. Demo

CodePudding user response：

First, change your stored procedure to convert selected columns from array to csv like this.

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

Then call it as:

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

CodePudding user response：

The same idea as that of @Zagarek only shorter using a scalar subquery.

create or replace procedure data_versioning_nonull
(new_table_name text, column_names text[], current_table_name text)
language plpgsql as
$$
begin
 execute format
 (
   'CREATE TABLE %I AS SELECT %s FROM %I',
   new_table_name, 
   (select string_agg(quote_ident(cn), ', ') from unnest(column_names) cn),
   current_table_name
 );
end;
$$;

and then

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