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.
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?
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
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.
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$ ;
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 🙂