create_text_table procedure explained with examples

examples.sql

-- see: https://postgresqlstan.github.io/postgresql/dynamic-sql/

create schema examples;
set schema 'examples';

CREATE PROCEDURE create_text_table (fields INT, tablename TEXT = '_import')
AS $$
/*
DESCRIPTION       create text-only staging table
  ARGUMENTS       fields: number of fields (columns)
                  tablename: [optional, default='_import'] name of table
*/
DECLARE
  fieldlist TEXT := '';
BEGIN
  SELECT string_agg(field, ', ' ORDER BY n)
    FROM (SELECT 'f' || n || ' text' AS field, n
            FROM generate_series(1, fields) AS gs(n) ) sq
    INTO fieldlist;

  EXECUTE FORMAT('CREATE UNLOGGED TABLE %I (%s)', tablename, fieldlist);

  RAISE NOTICE '✅ created table % (% text fields)', tablename, fields;
END; $$
LANGUAGE plpgsql;

COMMENT ON PROCEDURE create_text_table(INT, TEXT) IS
  'create unlogged table with specified # of text fields';

-- usage examples:

call create_text_table (10);

call create_text_table (2, 'i2');

-- psql variables

\set fields 3

\echo :fields
select :fields;

\set tablename notcolumn
\echo :tablename
select :tablename;

select :'tablename';

-- generate field definitions: contruct the query

\set fields 3
select * from generate_series(1, :fields);

select n from generate_series (1, :fields) as gs(n);

select n, 'f' || n || ' text' as field
   from generate_series(1,:fields) as s(n);

select string_agg(field, ', ' order by n) as fieldlist
  from (select n, 'f' || n || ' text' as field
        from generate_series (1, :fields) as gs(n) ) sq;

\gset
\echo :'fieldlist'

select 1 as fields, 'demo' as tablename \gset
select :fields, :'tablename';

-- contruct/execute dynamic sql

select format('create unlogged table %I (%s)', :'tablename',:'fieldlist');

\set tablename 'quote test'
select format('create unlogged table %I (%s)', :'tablename',:'fieldlist');

\gexec

\d "quote test"

-- raise notice, finished

do $$ begin raise notice 'demo finished'; end $$ ;