I want to create a function or stored procedure that will create the table in PostgreSQL with its columns based from the parameter passed through the function.
The example of the parameter will be in jsonb type, like this:
{
"columns": [
{
"name": "name",
"dataType": "Text",
"isRequired": true
},
{
"name": "type",
"dataType": "Text",
"isRequired": false
},
{
"name": "id",
"dataType": "Text",
"isRequired": true
}
]
}
What I would like to achieve is create the column based on the name field along with their dataType and isRequired will represents the NULL or NOT NULL.
Currently I have my function look like this:
CREATE OR REPLACE FUNCTION create_table(tableName text, metadata jsonb) RETURNS VOID AS
$$
BEGIN
EXECUTE (
SELECT format('CREATE TABLE %s (%s)', $1, c.cols)
FROM $2
CROSS JOIN LATERAL (
SELECT string_agg(quote_ident(col->>'name')
|| ' ' || (col->>'dataType')::regtype, ', ') AS cols
FROM json_array_elements($2 -> 'columns') col
) c
);
END;
$$
LANGUAGE plpgsql VOLATILE;
But this gave me error at the FROM $2 as:
ERROR: syntax error at or near "$2".
And also I am not sure how I can get the NULL or NOT NULL out of the isRequired field.
Ultimately I wanted a query look like this:
CREATE TABLE type_1 (
name TEXT NOT NULL,
type TEXT NULL,
id TEXT NOT NULL REFERENCES other_table (id)
);
Along with the foreign key settings.
If anyone knows how to do this I deeply appreciated it, thank you!
