>
>
>
>> SET
>> g = year % 19,
>> c = year / 100,
>> h = (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30,
>> i = h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)),
>> j = year + year/4 + i + 2 - c + c/4) % 7,
>> p = i - j,
>> easter_month = 3 + (p + 26)/30,
>> easter_day = 1 + (p + 27 + (p + 6)/40) % 31
>> SELECT make_date(year, easter_month, easter_day)
>>
>> or maybe even WITH like this:
>>
>> WITH
>> year % 19 AS g ,
>> year / 100 AS c,
>> (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h,
>> h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i,
>> year + year/4 + i + 2 - c + c/4) % 7 AS j,
>> i - j AS p,
>> 3 + (p + 26)/30 AS easter_month,
>> 1 + (p + 27 + (p + 6)/40) % 31 AS easter_day
>> SELECT make_date(year, easter_month, easter_day)
>>
>
> I do not think this clause is necessary (because we have PLpgSQL or C),
> but other people can have different opinions (and it is true, so this
> feature can have some performance benefit - because it enhances the
> possibilities of inlined expressions and custom (own) extensions are
> prohibited in cloud environments (and will be) ). Theoretically the
> implementation of this feature should not be hard, because these variables
> are very local only (the scope is just row), so this is just a game for
> parser and for expression's interpreter. But if you introduce this feature,
> then it is better to use syntax that is used by some other well known
> systems (Oracle or others).
>
The name for this feature can be "row scope variables" and yes, in OLAP
queries there are repeated expressions where this feature can be useful.
postgres=# explain verbose select make_date(year, easter_month,
easter_day) from (select year, 3 + (p + 26)/30 AS easter_month, 1 + (p +
27 + (p + 6)/40) % 31 AS easter_day from ( select year, i - j AS p from
(select year, i, (year + year/4 + i + 2 - c + c/4) % 7 AS j from (select
year, c, h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i from
(select year, g, c, (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h from
(select year, year % 19 as g, year / 100 as c from
generate_series(2019,2020) g(year) offset 0) s1 offset 0) s2 offset 0) s3
offset 0) s4 offset 0) s5 offset 0) s6;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY
PLAN │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s6 (cost=0.00..0.35 rows=2 width=4)
│
│ Output: make_date(s6.year, s6.easter_month, s6.easter_day)
│
│ -> Subquery Scan on s5 (cost=0.00..0.33 rows=2 width=12)
│
│ Output: s5.year, (3 + ((s5.p + 26) / 30)), (1 + (((s5.p + 27) +
((s5.p + 6) / 40)) % 31)) │
│ -> Subquery Scan on s4 (cost=0.00..0.26 rows=2 width=8)
│
│ Output: s4.year, (s4.i - s4.j)
│
│ -> Subquery Scan on s3 (cost=0.00..0.24 rows=2 width=12)
│
│ Output: s3.year, s3.i, ((((((s3.year + (s3.year / 4))
+ s3.i) + 2) - s3.c) + (s3.c / 4)) % 7) │
│ -> Subquery Scan on s2 (cost=0.00..0.18 rows=2
width=12) │
│ Output: s2.year, s2.c, (s2.h - ((s2.h / 28) *
(1 - (((s2.h / 28) * (29 / (s2.h + 1))) * ((21 - s2.g) / 11))))) │
│ -> Subquery Scan on s1 (cost=0.00..0.10
rows=2 width=16) │
│ Output: s1.year, s1.g, s1.c, (((((s1.c -
(s1.c / 4)) - (((8 * s1.c) + 13) / 25)) + (19 * s1.g)) + 15) % 30) │
│ -> Function Scan on
pg_catalog.generate_series g (cost=0.00..0.03 rows=2 width=12)
│
│ Output: g.year, (g.year % 19),
(g.year / 100) │
│ Function Call:
generate_series(2019, 2020)
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(15 rows)
Pavel
