[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] prepared statement in crosstab query
From:       David Johnston <polobo () yahoo ! com>
Date:       2012-09-01 2:24:30
Message-ID: 5E01D39F-F23A-4DAA-B826-60B2BFABD520 () yahoo ! com
[Download RAW message or body]

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,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


[prev in list] [next in list] [prev in thread] [next in thread]