Add a column with name as a result from a SELECT

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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$
;

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply