postgresql如何循环读取填充同一张表

Question

例如我现在在oth模式下有一张表oth.power
有三个属性month，day，fee
目前有两行数据：6 1 14
6 2 8
这张表格第一第二列的规律分别是：第一列永远是6，第二列每到一行就+1
而第三列的规律是满足斐波那契数列关系，从第三行开始，每一行的fee值都是上面两行的fee值之和
可以推断，第三行的数据为：6 3 22， 第四行为：6 4 30。。。。。。依次类推
现在需要我在这两行数据的基础上把这张表填充到共有30行为止。
虽然我可以类推，但是这样需要我人工去一行一行的用insert 的语句去插入数据，请问能否写一个sql语言使得可以自动进行这个插入的步骤？

Answer 1

直接写sql不好搞，除非是存储过程。
用其它语言生成sql语句是最简单，比如用python:

class Fibonacci(object):

    def __init__(self, n):
        self.n = n
        self.current = 0
        self.a = 14
        self.b = 8

    def __next__(self):
        if self.current == 0:
            self.current += 1
            return self.a

        if self.current < self.n:
            self.a, self.b = self.b, self.a + self.b
            self.current += 1
            return self.a
        else:
            raise StopIteration

    def __iter__(self):
        return self


if __name__ == '__main__':
    day = 1
    fib = Fibonacci(30)
    # INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
    for num in fib:
        print(f"insert into oth.power (month，day，fee) values (6, {day}, {num});")
        day += 1

结果是：

insert into oth.power (month，day，fee) values (6, 1, 14);
insert into oth.power (month，day，fee) values (6, 2, 8);
insert into oth.power (month，day，fee) values (6, 3, 22);
insert into oth.power (month，day，fee) values (6, 4, 30);
insert into oth.power (month，day，fee) values (6, 5, 52);
insert into oth.power (month，day，fee) values (6, 6, 82);
insert into oth.power (month，day，fee) values (6, 7, 134);
insert into oth.power (month，day，fee) values (6, 8, 216);
insert into oth.power (month，day，fee) values (6, 9, 350);
insert into oth.power (month，day，fee) values (6, 10, 566);
insert into oth.power (month，day，fee) values (6, 11, 916);
insert into oth.power (month，day，fee) values (6, 12, 1482);
insert into oth.power (month，day，fee) values (6, 13, 2398);
insert into oth.power (month，day，fee) values (6, 14, 3880);
insert into oth.power (month，day，fee) values (6, 15, 6278);
insert into oth.power (month，day，fee) values (6, 16, 10158);
insert into oth.power (month，day，fee) values (6, 17, 16436);
insert into oth.power (month，day，fee) values (6, 18, 26594);
insert into oth.power (month，day，fee) values (6, 19, 43030);
insert into oth.power (month，day，fee) values (6, 20, 69624);
insert into oth.power (month，day，fee) values (6, 21, 112654);
insert into oth.power (month，day，fee) values (6, 22, 182278);
insert into oth.power (month，day，fee) values (6, 23, 294932);
insert into oth.power (month，day，fee) values (6, 24, 477210);
insert into oth.power (month，day，fee) values (6, 25, 772142);
insert into oth.power (month，day，fee) values (6, 26, 1249352);
insert into oth.power (month，day，fee) values (6, 27, 2021494);
insert into oth.power (month，day，fee) values (6, 28, 3270846);
insert into oth.power (month，day，fee) values (6, 29, 5292340);
insert into oth.power (month，day，fee) values (6, 30, 8563186);

然后把生成好的sql去数据库执行一下就好了