[PostgreSQL] temp table 搭配 for loop

終於用很髒的方式兜出來了。

CREATE TEMPORARY TABLE ExpectedRoe
(
    stock_code text NOT NULL,
    expected_roe double precision
);

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT distinct(stock_code) as stock_code FROM BalanceSheet
    LOOP
        insert into ExpectedRoe(stock_code, expected_roe)
        select
            r.stock_code, expected_roe
        from ...; -- 用 r.stock_code 當參數
    END LOOP;
END$$;

SELECT * from ExpectedRoe where expected_roe >= 0.1 
order by expected_roe desc;

某個例子:

DROP TABLE IF EXISTS AllRoe;
CREATE TEMPORARY TABLE AllRoe
(
    stock_code text NOT NULL,
    roe double precision
);

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT distinct(stock_code) as stock_code FROM BalanceSheet
    LOOP
        insert into AllRoe(stock_code, roe)
        select
            r.stock_code, T.roe
        from 
        (
            with IndividualRoe as 
            (
                select activity_date, roe from roe(r.stock_code)
            )
            select roe from IndividualRoe
            where activity_date in
            (
                select max(activity_date) from IndividualRoe
            )
        ) as T;
    END LOOP;
END$$;

select * from AllRoe;