On Aug 31, 2012, at 21:53, Samuel Gendler <sgend...@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,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. > > 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 a single parameter/input. Convert that string to an array and then use the =ANY array operator to match the column against the array. You could also just pass an array but I haven't tried that in Java but I do know how to pass strings and let 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-queries as literals. Lookup the various quote_ functions the help protect yourself if you do this. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
