Returning empty data from dynamic pivot is there is no data

Question

Code below from how to preserve column names on dynamic pivot is used to create dynamic pivot table.

If source table contains no data, sql error occurs since create table column list end with comma (there are no pivot columns). How to fix this so that empty table is returned ?

To reproduce, remove insert commands

insert into sales values ( '2016-1-1', 'Ø 12.3/3mm', 2);
insert into sales values ( '2016-1-1', '+-3,4%/3mm', 52);
insert into sales values ( '2016-1-3', '/3,2m-', 246);

from code.

testcase:

create temp table sales ( saledate date, productname char(20), quantity int );
insert into sales values ( '2016-1-1', 'Ø 12.3/3mm', 2);
insert into sales values ( '2016-1-1', '+-3,4%/3mm', 52);
insert into sales values ( '2016-1-3', '/3,2m-', 246);

do $do$


declare
voter_list text;
begin

create temp table myyk on commit drop as
select saledate as kuupaev,
  format ('"%s"', replace (upper(productname), ' ', '')) as tootjakood,               
  sum(quantity)::int as kogus 
from sales
group by 1,2
;

drop table if exists pivot;

voter_list := (
    select string_agg(distinct tootjakood, ' ' order by tootjakood) from myyk
    );

execute(format('
    create table pivot (
kuupaev date,
        %1$s
    )', (replace(voter_list, ' ', ' integer, ') || ' integer')
));

execute (format($f$

  insert into pivot
        select
           kuupaev,
            %2$s
        from crosstab($ct$
            select
                kuupaev,tootjakood,kogus
            from myyk
            order by 1
            $ct$,$ct$
            select distinct tootjakood
            from myyk
            order by 1
            $ct$
        ) as (
            kuupaev date,
            %4$s
        );$f$,

        replace(voter_list, ' ', ' + '),
        replace(voter_list, ' ', ', '),
        '',
        replace(voter_list, ' ', ' integer, ') || ' integer'  -- 4.
    ));
end; $do$;

select * from pivot;

Postgres 9.1 is used.

klin · Accepted Answer · 2016-02-08T11:14:08.447

1

Insert an exception handler at the bottom of the DO block body. You can silently ignore errors and create dummy pivot table:

...
exception
    when others then
        drop table if exists pivot;
        create table pivot ("No data" text);
end; $do$;

or raise an exception with your own error message:

...
exception
    when others then
        drop table if exists pivot;
        raise exception 'There is no data in the source dataset.'
end; $do$;

You can also use if-then-else statement:

...
drop table if exists pivot;

if (select count(*) from myyk) > 0 then 
    voter_list := (
        select string_agg(distinct tootjakood, ' ' order by tootjakood) from myyk
        );
    ...
    ...
else
    create table pivot ("No data" text);
end if;
end; $do$;

edited Feb 08 '16 at 11:14

answered Feb 08 '16 at 10:26

klin

112,967
15
204
232

I prefer not use exception handler. How to check voter_list is empty or if myyk table contains no records and return empty table in this case ? Or is it possible to force crosstab to return empty table if there are no rows ? – Andrus Feb 08 '16 at 10:50
There is related question in http://stackoverflow.com/questions/35267989/how-to-put-column-to-end-of-dynamic-pivot-table For empty table check, is it possible to use plpgsql `IF` command to return empty table if crosstab cannto return it ? – Andrus Feb 08 '16 at 11:00
Thank you. To avoid extra table scan I used `if voter_list is null then` `create temp table pivot ("No data" text) on commit drop;` `return;` `end if;` is this OK? – Andrus Feb 08 '16 at 12:34
Yes, `return` is ok to avoid an error when there is no data. – klin Feb 08 '16 at 12:51

Returning empty data from dynamic pivot is there is no data

1 Answers1

Linked