Add a column with name as a result from a SELECT

Question

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.

EDIT:

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! The error: SQL Error [42601]: ERROR: type modifiers must be simple constants or identifiers

Answer 1

Solved my problem with the following function:

CREATE OR REPLACE FUNCTION client_schema.update_geometry_type(my_table text, my_schema text, OUT update_result boolean)
LANGUAGE plpgsql
AS $function$
   declare new_projection varchar(255);
   declare my_database varchar(255);
   declare old_projection varchar(255);
   begin
   CREATE extension if not EXISTS dblink;
   select split_part(current_database(), '-', 1) into my_database;
   select c.projection_key from client_schema.dblink('dbname=adm-3','SELECT projection_key, client_name FROM "adm-3".public.client as c ')
   AS c(projection_key text, client_name text) where c.client_name = my_database into new_projection;
   select Find_SRID(my_schema, my_table, 'geom') into old_projection ;
   select split_part(new_projection , ':', 2) into new_projection;
   EXECUTE 'ALTER TABLE '
   || my_schema::text || '.' || my_table::text 
   || ' ALTER COLUMN geom type geometry(Polygon, ' || new_projection ||') USING ST_Transform(geom, ''EPSG:' || old_projection || ''', ' || new_projection || ')';
   END;
$function$
;

Answer 2

You can have SQL that produces create table statement. It can produce inserts, or something. That way, you can move data between different DB systems, and so on.