通过PostgreSQL中的循环添加多个列 [英] adding multiple columns via a loop in postgresql

问题描述

我正在尝试在具有多个通过索引命名的列的架构中创建一个新表。

I'm trying to create a new table in a schema that has multiple columns, named via an index.

CREATE TABLE rflux (pk SERIAL PRIMARY KEY NOT NULL);

现在我想添加新列，例如col0 FLOAT，col1，col2，col3等。直到colN。

Now I want to add new columns like col0 FLOAT, col1, col2, col3, .... up to colN.

我知道我可以做类似的事情

I know I can do something like

ALTER TABLE rflux add column col0 FLOAT add column col1 FLOAT ... ;

但我不想输入所有内容，因为我需要创建约4500列。我正在尝试通过循环来实现此功能，但无法完全实现它。有人有什么想法吗？我试过创建一个函数来执行此操作..

but I don't want to type everything out, since I need to create ~4500 columns. I'm trying to implement this with loops but I can't quite get it working. Does anyone have any ideas? I've tried creating a function to do this ..

create function addColumns()
returns void
as $$
begin
for i in 1..10 loop
alter table rflux add column tmp float;
alter table rflux rename tmp to col||i;
end loop;
return;
end;
$$
language plpgsql;

然后执行
select * from addColumns（）;

then do select * from addColumns();

，但是将列重命名为col || i时，甚至在尝试i时，我都会收到错误消息。我什至不确定这是否是最好的方法。如何添加多个列，以便可以使用计数器增加列的名称？

but I get errors when renaming the column to col||i , or even just when I try i. I'm not even sure if this is the best way to do this. How do I add multiple columns where I can increment the name of the column with a counter?

Edit ..我知道我无法使用4500列来做到这一点，但是如果我想做10列，该问题的解决方案是什么？

Edit..I know I can't do this with 4500 columns, but what's the solution to this problem if I wanted to do it for 10 columns, say?

谢谢。

推荐答案

是否有帮助：

-- VERSION : POSTGRESQL 9.3

-- FICTIVE TABLE #1

CREATE TABLE table_1 ("YEAR" int, "CODE_SP" text, "TOTAL" int);

INSERT INTO table_1 VALUES
(2035, 'TRUC', 2),
(2035, 'MUCH', 4),
(2036, 'FLIC', 7),
(2036, 'LORD', 2),
(2036, 'TRUC', 8),
(2037, 'MUCH', 2),
(2037, 'FLIC', 2),
(2037, 'FLAC', 5),
(2037, 'MORD', 9),
(2038, 'LOOP', 3),
(2038, 'MUCH', 3);

SELECT * FROM table_1;

-- FICTIVE TABLE #2

CREATE TABLE table_2 ("YEAR" int);

INSERT INTO table_2("YEAR")
SELECT serial
FROM generate_series(2035,2038,1) AS serial;

SELECT * FROM table_2;

-- LOOP FOR ADDING COLUMNS ON TABLE #2 

DO
$do$
    DECLARE colnames TEXT;
BEGIN
FOR colnames IN 
    SELECT "CODE_SP"
    FROM table_1
    GROUP BY "CODE_SP"
    ORDER BY "CODE_SP"
LOOP
    EXECUTE 'ALTER TABLE table_2 ADD COLUMN ' || quote_ident(colnames) || ' INT DEFAULT NULL;'; /* careful: in quoted text, the spaces are important */
END LOOP;
END
$do$;

-- LOOP FOR COMPLETTING CELLS ON TABLE #2 

DO
$do$
    DECLARE id_value TEXT;
BEGIN
FOR id_value IN
    SELECT "CODE_SP"
    FROM table_1
    GROUP BY "CODE_SP"
    ORDER BY "CODE_SP"
LOOP
    EXECUTE 'UPDATE table_2 SET ' || quote_ident(id_value) || ' = table_1."TOTAL" FROM table_1 WHERE table_1."CODE_SP" = ' || quote_literal(id_value) || ' AND table_1."YEAR" = table_2."YEAR";'; /* careful: in quoted text, the spaces are important */
END LOOP;
END
$do$;

这篇关于通过PostgreSQL中的循环添加多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！