Re: prepared statement in crosstab query - Mailing list pgsql-sql

On Aug 31, 2012, at 21:53, Samuel Gendler <sgendler@ideasculptor.com> wrote:

> 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,a4061real,a4062 real,a4063 real,a4064 real,a4065 real,a4066 real,a4067 real,a4068 real,a4069 real,a4070 real,a4071
real,a4072real,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
thefollowing 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
postgresqlwon't allow me to use do parameter replacement in the inner queries.  Is this true? The query runs just fine
ifI manually construct the string, but some of those params are user input so I really don't want to just construct a
stringif 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,a4058real,a4059 real,a4060 real,a4061 real,a4062 real,a4063 real,a4064 real,a4065 real,a4066 real,a4067 real,a4068
real,a4069real,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
storedprocs, since all of the lists are of varying lengths, as are the number of returned columns (which always matches
thelength of the last 3 lists plus 1. 
>
>

Question marks inside a string have no special meaning.

Select * from crosstab(?,?) would work fine but the values you pass are still just literal strings

All those "?" are a pain syntax wise.  Consider the following (concept, syntax may need tweaking)

Select * from numbers where num = ANY ( split_to_array($$'1,3,5,7,11'$$, ',')::int[] )

In this case you pass a single delimited string (replacing the $-quoted literal shown) with whatever values you want as
asingle parameter/input.  Convert that string to an array and then use the =ANY array operator to match the column
againstthe array.  You could also just pass an array but I haven't tried that in Java but I do know how to pass strings
andlet PostgreSQL convert them. 

Not much more help as I have not used the crosstab function...but it seems you probably will need to build the
sub-queriesas literals.  Lookup the various quote_ functions the help protect yourself if you do this. 

David J.