How to create table with its columns based from the parameter passed from function in PostgreSQL?

Question

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 json) RETURNS VOID AS
    $$
    BEGIN
        EXECUTE (SELECT format('CREATE TABLE IF NOT EXISTS %s (%s);', $1, y.cols)
                 FROM (SELECT string_agg((s.details ->> 'name')::text || ' ' || (s.details->>'dataType')::text || ' ' || CASE WHEN (s.details->>'isRequired') = 'true'
                                  THEN 'NOT NULL'
                                  ELSE 'NULL'
                                  END, '', '') AS cols
                       FROM (SELECT json_array_elements(metadata) as details) s ) y);
    END
    $$
LANGUAGE plpgsql VOLATILE;

But this gave me error: function string_agg(text, unknown, unknown) does not exist.

Ultimately I wanted a query look like this:

CREATE TABLE type_1 (
  name TEXT  NOT NULL,
  type TEXT  NULL,
  id   TEXT  NOT NULL
);

If anyone knows how to do this I deeply appreciated it, thank you!

You don't need to pass 3rd parameter in string_agg() and also try casting string_agg(expression, ' '::text) — maddy23285, Aug 16 '22 at 09:04

score 1 · Accepted Answer · answered Aug 16 '22 at 09:24

It works with a few tweaks:

removed undue third argument in string_agg, as remarked by @maddy23285
replaced positional argument reference ($1, $2), out of personal preference
Text => text, in the jsonb definition
assigning the constructed statement to a variable makes debugging easier ; I made the function return the statement as text for the purpose of the demo, revert to void if desired

Feel free to reformat to fix mixed case

/*
 * Create a table named tableName, from column definitions as jsonb.
 * https://stackoverflow.com/questions/73369116/how-to-create-table-with-its-columns-based-from-the-parameter-passed-from-functi#73369116
 */
CREATE OR REPLACE FUNCTION create_table(tableName text, metadata jsonb) RETURNS text AS
$def$
    
declare
    statement text;
    
BEGIN

    select format($$
        create table %s (
          %s
        );
        $$,
        quote_ident(tableName), -- caveat, some engines might require group by tableName
        string_agg(format('%s %s %s',
            quote_ident(col->>'name'),
            quote_ident(col->>'dataType'),
            case col->>'isRequired' when 'true' then 'not null' else 'null' end
        ), ', ')
    ) as statement
    from jsonb_array_elements(metadata -> 'columns') col
    into statement;

    -- use this to debug the statement, `listen debug;` in psql or other client
    -- perform pg_notify('debug', statement);

    execute statement;
    
    return statement;

END;
$def$
LANGUAGE plpgsql VOLATILE;

dbfiddle

Thank you so much this works! Just out of curious, if one of the field is `int` instead of `text`, which part shall I change? I see no fix for data type in here but `int` will not work. By the way `bool` works. — Sivvie Lim, Aug 16 '22 at 12:34
@Corene my bad, `quote_ident` adds double-quotes that the engine fails to interpret. Remove `quote_ident` around `col->>'dataType'` (but mind your input - dynamic create table sounds like a quick way to trouble ;) — mcadorel, Aug 16 '22 at 12:42

How to create table with its columns based from the parameter passed from function in PostgreSQL?

1 Answers1