postgresql - Returning empty data from dynamic pivot is there is no data -

code below how preserve column names on dynamic pivot used create dynamic pivot table.

if source table contains no data, sql error occurs since create table column list end comma (there no pivot columns). how fix empty table returned ?

to reproduce, remove insert commands

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

from code.

testcase:

create temp table sales ( saledate date, productname char(20), quantity int ); insert sales values ( '2016-1-1', 'Ø 12.3/3mm', 2); insert sales values ( '2016-1-1', '+-3,4%/3mm', 52); insert sales values ( '2016-1-3', '/3,2m-', 246);  $do$   declare voter_list text; begin  create temp table myyk on commit drop select saledate kuupaev,   format ('"%s"', replace (upper(productname), ' ', '')) tootjakood,                  sum(quantity)::int kogus  sales group 1,2 ;  drop table if exists pivot;  voter_list := (     select string_agg(distinct tootjakood, ' ' order tootjakood) myyk     );  execute(format('     create table pivot ( kuupaev date,         %1$s     )', (replace(voter_list, ' ', ' integer, ') || ' integer') ));  execute (format($f$    insert pivot         select            kuupaev,             %2$s         crosstab($ct$             select                 kuupaev,tootjakood,kogus             myyk             order 1             $ct$,$ct$             select distinct tootjakood             myyk             order 1             $ct$         ) (             kuupaev date,             %4$s         );$f$,          replace(voter_list, ' ', ' + '),         replace(voter_list, ' ', ', '),         '',         replace(voter_list, ' ', ' integer, ') || ' integer'  -- 4.     )); end; $do$;  select * pivot; 

postgres 9.1 used.

insert exception handler @ bottom of block body. can silently ignore errors , create dummy pivot table:

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

or raise exception own error message:

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

you can use if-then-else statement:

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