重命名 postgresql 数据库中的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11898433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rename column names in postgresql database
提问by onurozcelik
I want rename all column names to lower case in PostgreSQL database I have just coded a sql function. Below is the code.
我想将 PostgreSQL 数据库中的所有列名重命名为小写，我刚刚编写了一个 sql 函数。下面是代码。
CREATE OR REPLACE FUNCTION update_column_names() RETURNS boolean AS $$
DECLARE
aRow RECORD;
aRow2 RECORD;
tbl_name TEXT;
col_name TEXT;
new_col_name TEXT;
BEGIN
FOR aRow IN select table_name from information_schema.tables where table_schema='public' and table_type='BASE TABLE' LOOP
SELECT aRow.table_name INTO tbl_name from current_catalog;
FOR aRow2 IN select column_name from information_schema.columns where table_schema='public' and table_name = aRow.table_name LOOP
SELECT aRow2.column_name INTO col_name from current_catalog;
new_col_name:=lower(col_name);
RAISE NOTICE 'Table name:%',tbl_name;
RAISE NOTICE 'Column name:%',aRow2.column_name;
RAISE NOTICE 'New column name:%',new_col_name;
ALTER TABLE tbl_name RENAME COLUMN col_name TO new_col_name;
END LOOP;
END LOOP;
RETURN true;
END;
$$ LANGUAGE plpgsql;
The code above gives relation tbl_name does not exists. What is wrong with the code?
上面的代码给出的关系 tbl_name 不存在。代码有什么问题？
回答by derobert
You need to use dynamic SQLto do that; the table name can't be a variable.
您需要使用动态 SQL来做到这一点；表名不能是变量。
?
EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) || ' RENAME COLUMN '
|| quote_ident(col_name) || ' TO ' || quote_ident(new_col_name);
?
or similar
或类似
