Postgresql function to create table with dynamic table name?

Question

Let's say I have several users, each with their own set of contacts. Users get to select which 3rd parties have access to their contacts.

I could maybe create 1 large contact table and have an 'owner' column so that I can identify which contacts belong to which users. However, I'd then need to maintain row-level permissions so that I can restrict which 3rd parties do/don't have access to specific user contacts.

Instead, I think (and I could be mistaken here) it makes more sense to have a contact table for each user, e.g.: contact_e878df81_eba1_4a61_b592_30ac7100362a. I could then manage permissions in a separate table.

To create these 'dynamic contact tables', I have the following function:

CREATE OR REPLACE FUNCTION create_contact_table(IN tbl_name text) RETURNS INT AS $$
DECLARE
    table_name text;
BEGIN
    table_name = tbl_name;
    create table IF NOT EXISTS table_name
    (
        id serial  not null
            constraint test_pkey
                primary key,
        firstname varchar not null,
        lastname varchar not null,
        age int not null,
        address varchar not null,
        email varchar not null,
        created timestamp default CURRENT_TIMESTAMP
    );
    alter table table_name
        owner to postgres;
    RETURN 1;
END ;

$$ LANGUAGE plpgsql;

When I execute the function (create_contact_table(contact_e878df81_eba1_4a61_b592_30ac7100362a), the table is created, but the name of the table is table_name...

Why is that? How can I fix it so that the table name is correct?

No, it's better to have a single table for all owners. Row-level permissions are typically used in controlled environment with a few users at most. But... you seems to be designing an app for many simultaneous users. — The Impaler, Dec 30 '21 at 13:44
You need [dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) — , Dec 30 '21 at 14:30
"*it makes more sense to have a contact table for each user*" - no, absolutely not. In fact it's a terrible idea. — , Dec 30 '21 at 14:31
"*I could then manage permissions in a separate table*" - you can still do that, if you want, when using row-level security policies. — Bergi, Dec 30 '21 at 14:39
@Bergi No, I meant row-level security. That is typically done by crafting a view filtering by the "current user" and forbidding access to the underlying tables. — The Impaler, Dec 30 '21 at 15:10
@TheImpaler You mean one view per user, and each user gets a postgres role? I don't see how that's different from having a table per user. When you said "row-level permissions", I thought you meant [row security policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), that are defined for a single table and actually *do* work well for many users. — Bergi, Dec 30 '21 at 16:25
@Bergi No, it's a single view in the database. However, the filtering condition in the `WHERE` clause includes a predicate with the "current user", with the form `WHERE my_table.username = CURRENT_USER`. That way, each separate account sees different rows. — The Impaler, Dec 30 '21 at 19:05

score 1 · Answer 1 · answered Dec 31 '21 at 08:24

You can do it only using dynamic SQL, for example:

CREATE OR REPLACE FUNCTION create_contact_table(tbl_name text)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
declare
    v_sql text; 
    t1 text;
begin

    v_sql = '
    create table %I
    (
        id serial  not null
            constraint test_pkey
                primary key,
        firstname varchar not null,
        lastname varchar not null,
        age int not null,
        address varchar not null,
        email varchar not null,
        created timestamp default CURRENT_TIMESTAMP
    );
    ';
    
    raise notice 'SQL:: %', v_sql;

    EXECUTE format(v_sql, tbl_name);

    return true; 
          
END;
$function$
;

Postgresql function to create table with dynamic table name?

1 Answers1

Linked

Related