没有动态数据从动态数据透视表返回空数据

问题描述：

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

如果源表不包含任何数据，则由于创建表列列表以逗号结尾(没有枢轴列)，因此会发生sql错误. 如何解决这个问题，以便返回空表?

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);

来自代码.

测试用例:

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.

Postgres 9.1 is used.

答

在DO块主体的底部插入一个异常处理程序.您可以默默地忽略错误并创建虚拟pivot表:

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$;

您还可以使用if-then-else语句:

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$;

没有动态数据从动态数据透视表返回空数据

相关推荐