Create enum dynamically in a PostgreSQL database in Node

Viewed 363

I have a service, written in Node, that calls an endpoint at start-up, receives back an array of values, and the array should then be used to create an Enum in a PostgreSQL database. The enum is later used to create a table in the same database.

The usual query looks like this:

DO $$
    BEGIN
        IF
            NOT EXISTS (
                    SELECT 1 FROM pg_type WHERE typname = 'tableType'
                )
        THEN
            CREATE TYPE
                tableType
            AS
                ENUM (
                    firstType, secondType
                    );
        END IF;
    END $$;
CREATE TABLE IF NOT EXISTS new_table(
  id,
  enumValue tableType
);

Unfortunately, I am unable to transform the above query and make it dynamic.

I have tried the following approaches:

  1. With a single parameter
ENUM (
  $1
);

And then:

  ---
  const tableTypes = await axios.get('/tableTypes');

  await pgPool.query('BEGIN');
  const createEnumQuery = fs.readFileSync(path.resolve(__dirname, './sql/createEnum.sql')).toString();
  await pgPool.query(createEnumQuery, [tableTypes .data]); // or just tableTypes.data
  await pgPool.query('COMMIT');
  1. With multiple parameters by manually generating the SQL using a for loop
ENUM (
  $1, $2, $3, ...
);
  ---
  const tableTypes = await axios.get('/tableTypes');

  await pgPool.query('BEGIN');
  const createEnumQuery = fs.readFileSync(path.resolve(__dirname, './sql/createEnum.sql')).toString();
  await pgPool.query(createEnumQuery, tableTypes .data);
  await pgPool.query('COMMIT');

Is there a way to do this?

0 Answers
Related