Question
Last activity: 29 Jan 2016 14:56 EST
Install error on posgresql PRPC 719 in case of datestyle like 'iso, ymd'
Hi,
My customer had tried install prpc 719 with Postgresql 9.3 on CentOS.
Then had met an sql error which recommended different datestyle to be used.
Some regions' users use 'ymd' as their date style.
All insertion of date format was covered by "set datestyle 'iso, dmy'" at beginning of the script.
But some PL/SQLs were generated and no way to cover the execution.
Ex:
BEGIN
sqlStmt := ' SELECT min(usagePeriod) from ( Select (' || periodSql || ') as usagePeriod FROM ( select pzPeriodEnding as pzUsageDay from prpc7.pr_hourly_usage ) S
where pzUsageDay < DATE_TRUNC(''day'', current_date-1)+ interval ''86399 second''
AND pzUsageDay > (select COALESCE( max(pzUsageDay), date ''01/01/1990'' ) from prpc7.pr_daily_usage )
group by (' || periodSql || ')) D ' ;
EXECUTE sqlStmt into strict workingPeriod;
IF workingPeriod IS NULL THEN
raise notice 'No data to process(workingPeriod->%)', workingPeriod;
return;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise notice 'No data to process';
RETURN;
WHEN OTHERS THEN
RAISE;
END;
Current workaround is to change the "datestyle = 'iso, dmy' " onto postgresql.conf , is there any smart solution ?
I think the Generator will fix the issue for the future release.
Hi,
My customer had tried install prpc 719 with Postgresql 9.3 on CentOS.
Then had met an sql error which recommended different datestyle to be used.
Some regions' users use 'ymd' as their date style.
All insertion of date format was covered by "set datestyle 'iso, dmy'" at beginning of the script.
But some PL/SQLs were generated and no way to cover the execution.
Ex:
BEGIN
sqlStmt := ' SELECT min(usagePeriod) from ( Select (' || periodSql || ') as usagePeriod FROM ( select pzPeriodEnding as pzUsageDay from prpc7.pr_hourly_usage ) S
where pzUsageDay < DATE_TRUNC(''day'', current_date-1)+ interval ''86399 second''
AND pzUsageDay > (select COALESCE( max(pzUsageDay), date ''01/01/1990'' ) from prpc7.pr_daily_usage )
group by (' || periodSql || ')) D ' ;
EXECUTE sqlStmt into strict workingPeriod;
IF workingPeriod IS NULL THEN
raise notice 'No data to process(workingPeriod->%)', workingPeriod;
return;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise notice 'No data to process';
RETURN;
WHEN OTHERS THEN
RAISE;
END;
Current workaround is to change the "datestyle = 'iso, dmy' " onto postgresql.conf , is there any smart solution ?
I think the Generator will fix the issue for the future release.
Yas.