DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=kq9AN2aQwJEs6hFdFnYoTFDn9NMzJO9fJNyFQ4B48BLEs2ms/G5CffM+e5YfiFI3Bf/4xzC3/60vucBATDg6/pYQx6taQZfIMIHBamcvxyAoXu0ODqx7CGmfxNExl107yTQYrfbjS53G+d0SMfk0O9kDuCxG9F/jugXW+MgAos4=
	;
References: 
 <CAEV0TzCx1_O4F0=QvbVv7PRrk1QxK1N1Mg51C8kka0R2gt-zQA@mail.gmail.com>
In-Reply-To: 
 <CAEV0TzCx1_O4F0=QvbVv7PRrk1QxK1N1Mg51C8kka0R2gt-zQA@mail.gmail.com>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <5E01D39F-F23A-4DAA-B826-60B2BFABD520@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: prepared statement in crosstab query
Date: Fri, 31 Aug 2012 22:24:30 -0400
To: Samuel Gendler <sgendler@ideasculptor.com>

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 th=
e 2 inner queries:
>=20
> SELECT * FROM crosstab(
> $$
>     SELECT t.local_key,=20
>            s.sensor_pk,=20
>            CASE WHEN t.local_day_abbreviation IN (?,?,?,?,?,?,?) THEN q.dp=
oint_value=20
>            ELSE NULL=20
>            END as dpoint_value=20
>     FROM dimensions.sensor s
>     INNER JOIN dimensions.time_ny t
>         ON s.building_id =3D ?
>         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 =3D t.local_key
>         AND q.sensor_fk =3D s.sensor_pk
>     ORDER BY 1,2
> $$,
> $$
>     SELECT s.sensor_pk
>     FROM dimensions.sensor s
>     WHERE s.building_id =3D ?
>       AND s.sensor_pk IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,=
?,?,?,?)
>     ORDER BY 1
> $$
> ) q(time_key bigint, a4052 real,a4053 real,a4054 real,a4055 real,a4056 rea=
l,a4057 real,a4058 real,a4059 real,a4060 real,a4061 real,a4062 real,a4063 re=
al,a4064 real,a4065 real,a4066 real,a4067 real,a4068 real,a4069 real,a4070 r=
eal,a4071 real,a4072 real,a4073 real,a4074 real,a4075 real,a4076 real,a4077 r=
eal,a4078 real,a4079 real)
>=20
>=20
>=20
>=20
> 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 (th=
e counts do match), I always get an exception telling me the following
>=20
>=20
>=20
>=20
> [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, 407=
0, 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079, 201204020000, 20120=
4040000, 201204020000, 201204040000, 4052, 4053, 4054, 4055, 4056, 4057, 405=
8, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4068, 4069, 4070, 4=
071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079, 4, 4052, 4053, 4054, 40=
55, 4056, 4057, 4058, 4059, 4060, 4061, 4062, 4063, 4064, 4065, 4066, 4067, 4=
068, 4069, 4070, 4071, 4072, 4073, 4074, 4075, 4076, 4077, 4078, 4079]
>=20
> Caused by: org.postgresql.util.PSQLException: The column index is out of r=
ange: 1, number of columns: 0.
> 	at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterL=
ist.java:53)
> 	at org.postgresql.core.v3.SimpleParameterList.setStringParameter(Si=
mpleParameterList.java:118)
> 	at org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJ=
dbc2Statement.java:2184)
> 	at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJd=
bc2Statement.java:1303)
> 	at org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJd=
bc2Statement.java:1289)
> 	at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJd=
bc2Statement.java:1763)
> 	at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(Abstract=
Jdbc3gStatement.java:37)
> 	at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJd=
bc4Statement.java:46)
> 	at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(De=
legatingPreparedStatement.java:169)
> 	at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(De=
legatingPreparedStatement.java:169)
>=20
>=20
>=20
>=20
> I've tried a number of different escaping mechanisms but I can't get anyth=
ing 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 j=
ust fine if I manually construct the string, but some of those params are us=
er input so I really don't want to just construct a string if I can avoid it=
.
>=20
> Any suggestions?
>=20
> Or can I create a prepared statement and then pass it in as a param to ano=
ther prepared statement?
>=20
> Something like:
>=20
> SELECT * FROM crosstab(?, ?) q(time_key bigint, a4052 real,a4053 real,a405=
4 real,a4055 real,a4056 real,a4057 real,a4058 real,a4059 real,a4060 real,a40=
61 real,a4062 real,a4063 real,a4064 real,a4065 real,a4066 real,a4067 real,a4=
068 real,a4069 real,a4070 real,a4071 real,a4072 real,a4073 real,a4074 real,a=
4075 real,a4076 real,a4077 real,a4078 real,a4079 real)
>=20
> With each '?' being passed a prepared statement?  That'd be a really cool w=
ay to handle it, but it seems unlikely to work.
>=20
> 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 a=
re of varying lengths, as are the number of returned columns (which always m=
atches the length of the last 3 lists plus 1.
>=20
>=20

Question marks inside a string have no special meaning.

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

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

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

In this case you pass a single delimited string (replacing the $-quoted lite=
ral shown) with whatever values you want as a single parameter/input.  Conve=
rt that string to an array and then use the =3DANY array operator to match t=
he 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 co=
nvert them.

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

David J.