CODEWITHSUNDEEP
sqlpostgresqlsupabase
Internlive
Internlive

Reputation: 1

How to create tables in Supabase automatically?

I am currently experimenting with programming a Flutter app to keep track of school grades. I'm trying to create a new table in Supabase whenever a new user registers. The table will store their data. The trigger works, but unfortunately, I can't get the function to do what it's supposed to do.

My trigger looks like this:

create trigger create_database_for_new_user
after insert on auth.users for each row
execute function create_database_for_new_user ();

This is my function:

BEGIN
    DECLARE
        new_table_name TEXT; 
    BEGIN
        
        new_table_name := 'user_' || NEW.id;

       
        EXECUTE format('CREATE TABLE IF NOT EXISTS %I (
            id bigint generated by default as identity primary key,
            inserted_at timestamp with time zone default timezone(''utc''::text, now()) not null,
            updated_at timestamp with time zone default timezone(''utc''::text, now()) not null,
            date timestamp with time zone not null,      
            grade numeric not null,                    
            type text not null                       
        );', new_table_name);

        RETURN NEW;
    END;
END;

It would be great if that works, because that way i am able to save all the grades in one table for each user. Solving it this way is probably a bit more complicated, but the project is generally not designed for many users. Since I will be working more with Supabase in the future, I would be really interested in how this function can be implemented. I’m not very familiar with SQL yet, thank you in advance for your help!

Upvotes: 0

Views: 366

Answers (1)

Samira-PSAU
Samira-PSAU

Reputation: 1

Did you try this?

CREATE OR REPLACE FUNCTION create_database_for_new_user()
RETURNS TRIGGER AS $$
DECLARE
    new_table_name TEXT;
BEGIN
    -- Generate the table name based on the user's ID
    new_table_name := 'user_' || NEW.id;

    -- Create the table dynamically for the new user
    EXECUTE format('
        CREATE TABLE IF NOT EXISTS %I (
            id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
            inserted_at TIMESTAMP WITH TIME ZONE DEFAULT timezone(''utc''::text, now()) NOT NULL,
            updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone(''utc''::text, now()) NOT NULL,
            date TIMESTAMP WITH TIME ZONE NOT NULL,
            grade NUMERIC NOT NULL,
            type TEXT NOT NULL
        );', new_table_name);

    -- Return the new row after the table creation
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Upvotes: 0

Related Questions