PostgREST - How to create tables?

Question

I want to create a new table using postgREST, but I was not able to find anything about this in the documentation. Is it possible to create tables? If yes, how?

Sorry if this questions was already asked, but unfortunately I always found solutions for postgres (without t :D)

Thx :)

Answer 1

you can create stored function that creates the table for you, then call that function via postgrest

something like:

create or replace function create_foo_table() returns void as 
$$
begin

  execute format('create table foo (id int primary key)')

end 
$$ language plpgsql;

then call /rpc/create_foo_table in Postgrest

you'd need to reload Postgrest's schema cache after this, in order to read the table: https://postgrest.org/en/v7.0.0/admin.html?highlight=reload#schema-reloading

This likely has security implications, so be careful, especially if using dynamic SQL to create the table