I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table. E.g.:
ALTER TABLE NAMES ADD COLUMN (SELECT n.name FROM NAMES AS n WHERE n.name = 'Tom') VARCHAR(255) NULL;
Is this possible? Can I create a function to do the select work then just call it during the Alter Table code?
Context: If you want to know why I need this, I have lots of clients that uses geometry types and each one has a different EPSG, I need to create the "geom" column based on the client's geometry so, I want to make a select based on my client's name to get its geometry type to then create the column with that type.
I think I got something near to what I need:
CREATE OR REPLACE FUNCTION client_schema.update_geom(my_table text, my_schema text, OUT alter_result boolean) LANGUAGE plpgsql AS $function$ declare client_projection varchar(255); declare db_name varchar(255); begin CREATE extension if not EXISTS dblink; select split_part(current_database(), '-', 1) into db_name; select c.projection_key from client_schema.dblink('dbname=adm-3','SELECT projection_key, context_name FROM "adm-3".public.clients as c ') AS c(projection_key text, context_name text) where c.context_name = db_name into client_projection; EXECUTE 'ALTER TABLE ' || esquema::text || '.' || tabela::text || ' ALTER COLUMN geom type geometry(Polygon, split_part($1, '':'', 2))' INTO alter_result USING client_projection; END; $function$ ;
The problem now is that he don't let me update the column's type!
SQL Error : ERROR: type modifiers must be simple constants or identifiers