Question 90813 in PostgreSQL Question posted in
The official documentation can be found here.

How to call an array in stored procedure? – Postgresql

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

2

Answers


  1. 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');
    Login or Signup to reply.
  2. 0
    1. 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.
    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. 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');
    1. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search