prepared statement in crosstab query - Mailing list pgsql-sql
| From | Samuel Gendler |
|---|---|
| Subject | prepared statement in crosstab query |
| Date | |
| Msg-id | CAEV0TzCx1_O4F0=QvbVv7PRrk1QxK1N1Mg51C8kka0R2gt-zQA@mail.gmail.com Whole thread Raw |
| Responses |
Re: prepared statement in crosstab query
|
| List | pgsql-sql |
I have the following crosstab query, which needs to be parameterized in the 2 inner queries:
SELECT * FROM crosstab(
$$
SELECT t.local_key,
s.sensor_pk,
CASE WHEN t.local_day_abbreviation IN (?,?,?,?,?,?,?) THEN q.dpoint_value
ELSE NULL
END as dpoint_value
FROM dimensions.sensor s
INNER JOIN dimensions.time_ny t
ON s.building_id = ?
AND s.sensor_pk IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
AND t.local_key BETWEEN ? AND ?
LEFT OUTER JOIN (
SELECT f.time_fk, f.sensor_fk,
cast(avg(f.dpoint_value) as numeric(10,2)) as dpoint_value
FROM facts.bldg_4_thermal_fact f
WHERE f.time_fk BETWEEN ? AND ?
AND f.sensor_fk IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
GROUP BY 1,2) q
ON q.time_fk = t.local_key
AND q.sensor_fk = s.sensor_pk
ORDER BY 1,2
$$,
$$
SELECT s.sensor_pk
FROM dimensions.sensor s
WHERE s.building_id = ?
AND s.sensor_pk IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
ORDER BY 1
$$
) q(time_key bigint, a4052 real,a4053 real,a4054 real,a4055 real,a4056 real,a4057 real,a4058 real,a4059 real,a4060 real,a4061 real,a4062 real,a4063 real,a4064 real,a4065 real,a4066 real,a4067 real,a4068 real,a4069 real,a4070 real,a4071 real,a4072 real,a4073 real,a4074 real,a4075 real,a4076 real,a4077 real,a4078 real,a4079 real)
However, when I attempt to create a prepared statement in java (or groovy, or as a hibernate sqlQuery object) with the following set of parameters (the counts do match), I always get an exception telling me the following
[Mon, Tue, Wed, Thu, Fri, Sat, Sun, 4, 4052, 4053, 4054, 4055, 4056, 4057, 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4068, 4069, 4070, 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079, 201204020000, 201204040000, 201204020000, 201204040000, 4052, 4053, 4054, 4055, 4056, 4057, 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4068, 4069, 4070, 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079, 4, 4052, 4053, 4054, 4055, 4056, 4057, 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4068, 4069, 4070, 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079]
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:53)
at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2184)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1303)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1289)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1763)
at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37)
at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
I've tried a number of different escaping mechanisms but I can't get anything to work. I'm starting to think that postgresql won't allow me to use do parameter replacement in the inner queries. Is this true? The query runs just fine if I manually construct the string, but some of those params are user input so I really don't want to just construct a string if I can avoid it.
Any suggestions?
Or can I create a prepared statement and then pass it in as a param to another prepared statement?
Something like:
SELECT * FROM crosstab(?, ?) q(time_key bigint, a4052 real,a4053 real,a4054 real,a4055 real,a4056 real,a4057 real,a4058 real,a4059 real,a4060 real,a4061 real,a4062 real,a4063 real,a4064 real,a4065 real,a4066 real,a4067 real,a4068 real,a4069 real,a4070 real,a4071 real,a4072 real,a4073 real,a4074 real,a4075 real,a4076 real,a4077 real,a4078 real,a4079 real)
With each '?' being passed a prepared statement? That'd be a really cool way to handle it, but it seems unlikely to work.
Doing the whole thing in a stored proc isn't really easily done - at least with my limited knowledge of creating stored procs, since all of the lists are of varying lengths, as are the number of returned columns (which always matches the length of the last 3 lists plus 1.