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

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!

CodePudding user response：

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