How to call an array in stored procedure?

Question

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

Zegarek · Accepted Answer · 2022-11-24 08:00:24Z

Using execute format() lets you replace all the quote_ident() with %I placeholders in a single text instead of a series of concatenated snippets. %1$I lets you re-use the first argument.
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.
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
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. Interestingly, exceeding specified varchar length would just convert it to unlimited varchar, making it just syntax noise.

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

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

P.R · Accepted Answer · 2022-11-23 19:00:07Z

0

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

answered Nov 23, 2022 at 19:00

P.R

844 bronze badges

1 Comment

Chris Over a year ago

Error :( tho that three in array are the existing columns in the sales_2019 table ERROR: column "{"orderid", "product", "purchaseaddress"}" does not exist

Collectives™ on Stack Overflow

How to call an array in stored procedure?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related