I’m trying to get a crosstab or pivot table working via the postgres crosstab function. It works fine when I execute the query on the database using a TO_TIMESTAMP to cast a string to a timestamp. However when I try to set up dynamic parameters everything falls apart in ignition for the most part I get row out of bounds errors in which it returns no rows. I’ve passed timestamp parameters through hundreds of queries but never have had an issue until I tried doing it with a crosstab function. I’d use the crosstab on the report module but it does not seem to be able to order both the columns and the rows correctly.
Anyone have any experience with this?
Try an explicit cast to timestamp (or timestamptz) after your replacement parameter. Something like this:
SELECT * FROM someTable WHERE t_stamp = ?::timestamptz
I made a work around for this issue if anyone views this in the future. I created another table with a single row with 3 columns, an index, and startTime/endTime. My script updates these start and end times on the new table where index = 1, and then the query compares the timestamps between tables rather than comparing it to a timestamp given by the script. This method doesnt require any parameters to be passed through the crosstab function now and appears to be working.
Kind of a sloppy workaround but it does work.
I have struck the same problem with attempting to pass integer parameters to the crosstab function in Postgres, I also need to utilise timestamps too.
The crosstab query works as expected when invoked without any parameters using runQuery, but breaks as soon as you try runPrepQuery with parameters. I tried extra quoting of the ? in the SQL query, but no success!
I will raise a ticket with tech support as I really need crosstab to work with parameters - any other suggestions would be greatly appreciated !
Don’t quote the ? parameters, explicitly cast them to the types you need. PostgreSQL syntax would be something like ?::int to force integer handling of that parameter. The crosstab function doesn’t have fixed parameter types, so there’s no way for the SQL parser to infer them.
1 Like
Thanks Phil - I tried your suggestion, but no success. Seems to be same as Brendan found for explicitly trying to cast the timestamps.
I am running on postgresql 9.2.24 on centos 7.6 with Ignition 7.9.9, everything else runs well for all other db interactions with ignition, so this is percurliar to crosstab().
Borrowing from Brendan’s last post, I have now developed a work around by utilizing a Postgres TEMP TABLE to hold the 4 parameters to drive the crosstab query, then its a simple JOIN on the SQL Query as the first argument to crosstab(text, text) function. It works nicely so far …
Have you managed to pass parameters to crosstab(text, text) successfully before ?
1 Like