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