Re: set_config() documentation clarification - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: set_config() documentation clarification |
| Date | |
| Msg-id | CAFj8pRDoAQVeyWLXQ1krdq-1yq1uXgbrfQRNnkEJ9WL38Z=gbw@mail.gmail.com Whole thread Raw |
| In response to | Re: set_config() documentation clarification (Pavel Stehule <pavel.stehule@gmail.com>) |
| List | pgsql-hackers |
SETg = 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) % 31SELECT make_date(year, easter_month, easter_day)or maybe even WITH like this:WITHyear % 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_daySELECT 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)
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 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
pgsql-hackers by date: