CODEWITHSUNDEEP
postgresqlinner-joinpostgresql-9.4
Paweł BB Drozd
Paweł BB Drozd

Reputation: 4913

Pass dynamic parameter to crosstab subquery

I have table like there:

 table:
 | id | key  | label  | amount |
 |----|------|--------|--------|
 | 1  | 1    | label1 | 10     |
 | 2  | 1    | label2 | 15     |
 | 3  | 1    | label3 | 99     |
 | 4  | 2    | label4 | 33     |
 | 5  | 2    | label2 | 10     |

fkey is a foreign key to other table.

I need to use results in crosstab and keep null values for these, where is no relation:

select * from crosstab(
   'with labels (lbl) as ( ' ||
   '  values (''label2''), (''label3''), (''label4'') ' ||
   ') ' ||
   'select 1, l.lbl, t.amount ' || 
   'from labels l ' ||
   '  left outer join "table" t on l.lbl = t.label and t.key = 1 ' ||
   'order by l.lbl' ) AS (
fkey integer, label1 integer, label2 integer, label3 integer)

and results is as I expected (for key = 1):

 |    | key  | label2 | label3 | label4 |
 |----|------|--------|--------|--------|
 | 1  | 2    | 15     | 99     | NULL   |

for key = 2

select * from crosstab(
   'with labels (lbl) as ( ' ||
   '  values (''label2''), (''label3''), (''label4'') ' ||
   ') ' ||
   'select 2, l.lbl, t.amount ' || 
   'from labels l ' ||
   '  left outer join "table" t on l.lbl = t.label and t.key = 2 ' ||
   'order by l.lbl' ) AS (
fkey integer, label1 integer, label2 integer, label3 integer)

result is:

 |    | key  | label2 | label3 | label4 |
 |----|------|--------|--------|--------|
 | 1  | 2    | 10     | NULL   | 33     |

Retrning key in line select 1, l.lbl, t.amount is hardcoded, because I need to keep query key if there is no relation (as t.key is returning NULL).

Now I need use this query as subquery for each keys in db. So I need something like:

with keys(key) as (
  values (1), (2), (3)
)
select * from keys
  left join (select * from crosstab(
    'with labels (lbl) as ( ' ||
    '  values (''label2''), (''label3''), (''label4'') ' ||
    ') ' ||
    'select ??, l.lbl, t.amount ' || 
    'from labels l ' ||
    '  left outer join "table" t on l.lbl = t.label and t.key = ?? ' ||
    'order by l.lbl' ) AS (
      key integer, label1 integer, label2 integer, label3 integer
    )
 ) I on (keys.key = I.key)

Is there way to bind key into place where are ?? marks?

Here is tester http://rextester.com/JMUBI41337 (with 2 in ?? marks what works only if key is 2).

Expected result:

 |    | key  | label2 | label3 | label4 |
 |----|------|--------|--------|--------|
 | 1  | 1    | 15     | 99     | NULL   |
 | 2  | 2    | 10     | NULL   | 33     |
 | 3  | 3    | NULL   | NULL   | NULL   |

Resolved. Link to working solution from Clodoaldo Neto: http://rextester.com/SYWQ20694

Upvotes: 0

Views: 909

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

lateraland format:

select *
from
    (values (1),(2),(3)) keys(key)
    left join lateral (
        select * from crosstab(format($$
            with labels (lbl) as (values
                ('label2'), ('label3'), ('label4')
            )
            select %1$s, l.lbl, t.amount
            from
                labels l
                left outer join
                t on l.lbl = t.label and t.key = %1$s
            order by l.lbl
        $$, keys.key)) as (
            key integer, label2 integer, label3 integer, label4 integer
        )
    ) i using (key)
;
 key | label2 | label3 | label4 
-----+--------+--------+--------
   1 |     15 |     99 |       
   2 |     10 |        |     33
   3 |        |        |

Upvotes: 1

Related Questions