Re: Explanation of tree-generating query

On Tue, Feb 2, 2016 at 4:27 PM, Guyren Howe <guyren(at)gmail(dot)com> wrote:

> I feel like I'm pretty decent with Postgres. But I saw the following query
> on the excellent Periscope blog. I've no idea how it works, and the various
> symbols involved are difficult to look up either with google or in the
> documentation. I believe the @ sign is probably ABS, but the <= clause in
> the consequent of a when-then is something I haven't seen before. I'm
> comfortable with the rest. Can someone explain how this works?
>
> with
> a as (
> select *
> from
> generate_series(0, 3, 1)
> )
> , b as (
> select *
> from
> generate_series(-3, 3, 1)
> )
> , tree as (
> select a.generate_series as t
> , b.generate_series as branch
> from a, b
> where
> case when mod(a.generate_series, 2) = 1
> then @ b.generate_series <= a.generate_series
> and mod(@ b.generate_series, 2) = 1
> else @ b.generate_series <= a.generate_series
> and mod(@ b.generate_series, 2) = 0
> end
> )
>
>
>
​I hope this wasn't for someones homework... :)​

​The "<=" operator is the basic less-than-or-equal operator for numbers
​
generate_series is a function that provides one row for every result of
stepping (by 1 in this case) from the starting value to the end value
inclusive.

"and" is, loosely, the boolean operator of the same name

"mod" := modulus; the remainder when performing integer division. In this
case the remainder when dividing by 2 is a test of odd/even

"b.generate_series" - the default name of the column in the query "SELECT *
FROM generate_series(...)" is the name of the function that was executed -
at least for this function: other functions can be defined to provide
different names.

​A := [0, 3]
B := [-3, 3]
FOR EACH combination of a,b (so 4 x 7 = 28 rows)
CASE WHEN (a IS ODD)
THEN (true if abs(b) <= a AND (b IS ODD), otherwise false)
ELSE (true if abs(b) <= a AND (b IS EVEN), otherwise false)
END

Since the CASE expression is in the WHERE clause the result required must
be a boolean - or NULL.

So in all cases only rows where abs(b) is less-than-or-equal-to a are
returned (I suppose this is like a symmetric matrix so you only need half
of the answers...)
Likewise, only return rows where the "even-ness" of a and b are the same
(both even or both odd)

An alternate way to express the non-CTE portion of the query would be:

pseudo-code:
CREATE FUNCTION is_even(num int) RETURNS boolean
AS
$$
SELECT mod(num, 2) = 0;
$$

​SELECT a.g_s AS t, b.g_s AS branch
FROM a
CROSS JOIN b
WHERE (abs(b) <= a)
AND is_even(b) = is_even(a);

​In times like this it would nice to be able to define temporary functions
just like you can use CTEs to define temporary views...not that big a deal
though.

Hope that helps.

David J.