0

I want to convert my SQL Server query into Postgres. Below is the query I have tried to convert and I am getting error in postgres

ERROR: syntax error at or near "crosstab" LINE 2:
crosstab(SUM(VALUE) FOR VARIABLE IN (8,9)) p ^ QUERY: select TIMESTAMP_S,COALESCE(8,0) AS variable_8_value,COALESCE(9,0) AS variable_9_value from ARCHIEVE_EXPORT_DB_AR3 t
crosstab(SUM(VALUE) FOR VARIABLE IN (8,9)) p CONTEXT: PL/pgSQL function somefuncname() line 12 at EXECUTE statement ********** Error **********

ERROR: syntax error at or near "crosstab" SQL state: 42601 Context: PL/pgSQL function somefuncname() line 12 at EXECUTE statement

Declare @sql varchar(max);
Declare @sql2 varchar(max);
set @sql= STUFF((select distinct ','+ QUOTENAME(VARIABLE) from ARCHIEVE_EXPORT_DB_AR FOR XML PATH('')) ,1,1,'');
set @sql2= STUFF((select distinct ','+ 'ISNULL(' + QUOTENAME(VARIABLE) + ',0)' + ' AS ' + QUOTENAME('variable_' + cast(VARIABLE as varchar) + '_value') from ARCHIEVE_EXPORT_DB_AR FOR XML PATH('')) ,1,1,'');

set @sql='select TIMESTAMP_S,' + @sql2 + ' from ARCHIEVE_EXPORT_DB_AR t  
               PIVOT (SUM(VALUE) FOR VARIABLE IN ('+@sql+')) p';


exec(@sql);

CREATE OR REPLACE FUNCTION somefuncname() RETURNS int LANGUAGE plpgsql AS $$
Declare 
v_sql text;
Declare 
v_sql2 text;
BEGIN
v_sql :=  STRING_AGG(Distinct(("VARIABLE"):: text), ',')   From "ARCHIEVE_EXPORT_DB_A3";
v_sql2 := STRING_AGG(Distinct('COALESCE(' || ("VARIABLE"):: text || ',0) AS variable_' || ("VARIABLE"):: text) || '_value', ',')   From "ARCHIEVE_EXPORT_DB_A3";

v_sql :='select TIMESTAMP_S,' || v_sql2 || ' from ARCHIEVE_EXPORT_DB_AR3 t  
               crosstab(SUM(VALUE) FOR VARIABLE IN (' || v_sql ||')) p';
               execute(v_sql);
END
$$;
SELECT somefuncname();
Parth Desai
  • 209
  • 2
  • 4
  • 19
  • it appears that you have a query like `select timestamp, another_select(instead of a list columns) from table t, etc` – valentin Dec 08 '15 at 07:54

1 Answers1

0

It appears from this answer that you aren't using crosstab correctly.

crosstab is in a module; have you included it? And the query within crosstab needs to be in quotes.

I suggest that you focus on getting a basic query that uses crosstab working first, before trying to use it in a procedure with dynamic SQL.

Community
  • 1
  • 1