Re: Explanation of tree-generating query

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tue, Feb 2, 2016 at 4:27 PM, Guyren Howe <guyren@xxxxxxxxx> 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.