改变表中所有列的数据类型(PostgreSQL 13)

Question

我有一个表import.hugo(导入是架构(，我需要将所有列的数据类型从text更改为numeric。该表已经有数据，所以为了更改列(例如(y，我使用以下内容：

ALTER TABLE import.hugo ALTER COLUMN y TYPE numeric USING (y::numeric); 

但是这个表有很多列，我不想手动操作。我在这里找到了一些东西，并这样尝试：

do $$
declare
t record;
begin
for t IN select column_name, table_name
from information_schema.columns
where table_name='hugo' AND data_type='text'         
loop
execute 'alter table ' || t.table_name || ' alter column ' || t.column_name || ' type numeric';
end loop;
end$$;

当我运行它时，它不起作用，它说：relation "hugo" does not exist我尝试了这个代码的许多变体，但我无法使它工作。我也不知道，如何实现这一部分：USING (y::numeric);(从第一个命令(到这个命令。

理想情况下，我需要它在一个函数中，用户可以在其中定义表的名称，我们在其中更改列的数据类型。所以函数像这样调用SELECT function_name(table_name_to_alter_columns);

谢谢。

Answer 1

从information_schema.columns中选择table_name是不够的，因为import模式不在search path中。您可以通过将import附加到SHOW search_path中显示的任何内容来将import模式添加到search_path中，也可以将table_schema列添加到您的DO块中：

do $$
declare
t record;
begin
for t IN select column_name, table_schema || '.' || table_name as full_name
from information_schema.columns
where table_name='hugo' AND data_type='text'         
loop
execute 'alter table ' || t.full || ' alter column ' || t.column_name || ' type numeric USING (' || t.column_name || '::numeric)';
end loop;
end$$;