Altering a column if it exists

Поиск
Список
Период
Сортировка
От Thara Vadakkeveedu
Тема Altering a column if it exists
Дата
Msg-id 1389216298.48861.YahooMailNeo@web125003.mail.ne1.yahoo.com
обсуждение исходный текст
Ответы Re: Altering a column if it exists
Список pgsql-novice
Дерево обсуждения

The following piece of code throws a plsql exception when run through jdbc... 
DO $$
                BEGIN
                                IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'position' and column_name='org_role_id')
                                THEN
                                                ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id;
                                END IF;
END$$;

SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [db-upgradeSchema.sql]: DO $$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'position' and column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id
                at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1548)
...
...
...
Caused by: org.springframework.dao.DataAccessResourceFailureException: Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [db-upgradeSchema.sql]: DO $$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'position' and column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id
                at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:56)
...
...
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [db-upgradeSchema.sql]: DO $$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'position' and column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id
                at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:202)
 ...
 ...
org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:47)
                ... 28 more
Caused by: org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at or near "$$ BEGIN IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'position' and column_name='org_role_id') THEN ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id"
  Position: 4
                at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
                at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
                at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
                at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
                at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
                at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
                at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
                at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
                at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
                at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:187)

Is there  a way to do this ?
thanks,
TG

В списке pgsql-novice по дате отправления: