postgresql-9.5 - 请将 oracle 视图转换为 postgresql

Question

CREATE VIEW HTWRK_AGEING_UNAGE_YYYYMM ("YEAR", "MONTH") AS SELECT YEAR, MONTH FROM (SELECT year, month FROM (SELECT TO_CHAR (n + 1994) year FROM (SELECT LEVEL n FROM DUAL CONNECT BY ROWNUM <= TO_CHAR (SYSDATE, ' YYYY') - 1994)) a, (SELECT LPAD (n, 2, '0') month FROM (SELECT LEVEL n FROM DUAL CONNECT BY ROWNUM <= 12)) b) 年份 || 月 <= TO_CHAR (SYSDATE, 'YYYYMM') UNION SELECT 'NA', 'NA' FROM DUAL;

标签： postgresql-9.5

Answer 1

这在 Postgres 中更容易

create view htwrk_ageing_unage_yyyymm 
as
select to_char(dt, 'yyyy') as year,
       to_char(dt, 'mm') as month
from generate_series(date '1995-01-01', date_trunc('month', current_date), interval '1 month')
union all
select 'NA', 'NA';