0

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

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

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.

1
  • Can you provide some kind of basic example of that? My answer worked for me but I'm interest in learning new methods of doing it! Apr 7 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.