Изменение текста на изменение символов не выполняется должным образом

Я изучаю PostgreSQL и пытаюсь создать функцию для ИЗМЕНЕНИЯ типов данных данной таблицы, которая имеет столбцы с типом данных character varying.

Для этого я создал курсор, который в основном запрашивает столбцы information_schema.columns в поисках таблиц, содержащих столбцы с типом character varying, чтобы позже выполнить изменение и попытаться изменить столбец на text, так что это поле не ограничено длиной Значение.

Вот функция:

CREATE OR REPLACE FUNCTION my_cursor(db_name TEXT, tbl_schema TEXT, tbl_name TEXT, col_type TEXT) RETURNS void AS $func$
DECLARE
   cid record;
BEGIN
   FOR cid IN SELECT * FROM information_schema.columns AS ic
              WHERE ic.table_catalog=db_name
                AND ic.table_schema=tbl_schema
                AND ic.table_name=tbl_name
                AND ic.data_type=col_type
   LOOP
      EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text', cid.table_schema, cid.table_name, cid.column_name);
   END LOOP;
   RETURN;
END;
$func$ LANGUAGE plpgsql;

SELECT my_cursor('database10232016', 'public', 'continent', 'character varying');

Функция компилируется и создается успешно, однако после запуска функции я заметил, что изменение не работает с указанным именем таблицы.

Можете ли вы помочь мне определить, что пошло не так с моим курсором?


postgresql sql cursor postgresql-9.4 sql-function
person fndg87    schedule 12.11.2016    source источник

Ответы (1)


arrow_upward
1
arrow_downward

В вашей функции сама по себе нет ничего плохого; он работает просто отлично. Однако несколько моментов:

  • Тип данных text аналогичен типу character varying без указания длины. Так что в основном вы усердно работаете, ничего не меняя, за исключением случаев, когда есть character varying (n) столбцов. В таблицах information_schema оба типа указаны как character varying, потому что это стандарт SQL; тип text является расширением PostgreSQL.
  • Вы не используете CURSOR, вы используете цикл. Это хорошо, избегайте курсоров, когда можете, потому что обычно есть более эффективные способы сделать что-то.
  • В вашей функции format() вы должны использовать заполнители вместо конкатенации строк с ||. Прямо сейчас вы объединяете строку, с которой вы больше не форматируете. Вместо этого используйте format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text', cid.table_schema, cid.table_name, cid.column_name).

Если у вас есть типы строк character (n) или character varying (n) и вы хотите снять ограничение длины, вам следует искать столбцы, где character_maximum_length IS NOT NULL в той же таблице.

person Patrick    schedule 13.11.2016
comment
Привет, Патрик, Спасибо, что обратили внимание на эти моменты. Однако, независимо от рекомендаций по типам данных, я все еще изо всех сил пытаюсь увидеть изменения, для которых предназначена ALTER TABLE. Не могли бы вы попробовать ту же функцию для локальной таблицы, в которой есть столбец с переменным типом символов. В результате столбцы с переменными символами должны превратиться в столбцы текстового типа. - person fndg87; 13.11.2016
comment
Я хочу сказать, что тип данных text никогда не будет отображаться для столбца. Это просто удобное обозначение для character varying. Что вы хотите, так это удалить ограничение длины, которое должно работать с вашей функцией, но вам лучше проверять character_maximum_length вместо типа данных столбца. - person Patrick; 13.11.2016
comment
Я понимаю вашу точку зрения. Теперь предположим, что у меня есть столбцы типа smallint в таблице, и я хочу изменить их на bigint. Рассмотрим функцию, написанную для изменения, и попробуйте, она не сработает. Например: SELECT my_cursor('database10232016', 'public', 'континент', 'smallint'); И часть Alter должна выглядеть так: format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE bigint', cid.table_schema, cid.table_name, cid.column_name) Это то, на что я пытаюсь указать. По какой-то причине он не запускает функцию изменения. - person fndg87; 13.11.2016
comment
Вы владелец стола? У вас есть необходимые разрешения? Можете ли вы запустить оператор из командной строки? - person Patrick; 13.11.2016
comment
Я понял, в чем моя проблема ... Это была глючная IDE pgadmin с сеансом смерти, и она еще имитировала эту работу. После того, как я запустил команду из консоли, все заработало. Спасибо за поддержку и советы. - person fndg87; 13.11.2016