Date: Fri, 21 Mar 2003 15:19:40 +0100
From: Eric Veldhuyzen <ericv@xs4all.net>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Eric Veldhuyzen <ericv@xs4all.net>, pgsql-sql@postgresql.org
Subject: Re: cast of integer to bool doesn't work (anymore?)
Message-ID: <20030321141940.GD6540@opium.xs4all.nl>
References: <20030321133646.GC6540@opium.xs4all.nl>
	<Pine.LNX.4.44.0303211553560.2751-100000@matrix.gatewaynet.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="f0KYrhQ4vYSV2aJu"
Content-Disposition: inline
In-Reply-To: <Pine.LNX.4.44.0303211553560.2751-100000@matrix.gatewaynet.com>
User-Agent: Mutt/1.5.3i

--f0KYrhQ4vYSV2aJu
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Mar 21, 2003 at 03:54:56PM -0200, Achilleus Mantzios wrote:
> On Fri, 21 Mar 2003, Eric Veldhuyzen wrote:
>=20
> > Hi,
> >=20
> > Here at my work we use on the production servers PostgreSQL version
> > 7.2.3, but I have version 7.3.2 (from the Debian distribution) on my
> > workstation. Now I noticed yesterday that the query 'select 0::boolean;'
> > works on the production server, it gives the output:
> >=20
> > # select 0::boolean;
> >  bool=20
> > ------
> >  f
> > (1 row)
> >=20
> > Perfect. But when I try this on my local version of postgreSQL I get
> > this:
> >=20
> > # select 0::boolean;
> > ERROR:  Cannot cast type integer to boolean
>=20
> Just wrap 0 with single quotes,
> e.g.
> # select '0'::boolean;

Yeah, I tried that, and yes, that does work from the psql prompt. This
basically means that I fool psql into thinking that it is a string, and
then forcing it to cast to a boolean, right? But my problem is that it
does not work when I use that in the prepare statement from perl. For
example:

my $sth =3D $dbh->prepare(
    "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,?::bool)");
$sth->execute('test', 0);

This code gives me the error
  "DBD::Pg::st execute failed: ERROR:  Cannot cast type integer to boolean"
on 7.3, but it works on 7.2 and below. If I change the prepare to=20

my $sth =3D $dbh->prepare(
    "INSERT INTO object_def (name, meant_as_subobject) VALUES (?,'?'::bool)=
");

I will get the error=20
  "execute called with 2 bind variables, 1 needed" because the
perl database driver can't handle the quote characters in a prepare stateme=
nt.

This here is ofcourse a simplyfied example, in our real project the
prepare statement is dynamically build by a library that knows the
database schema, and tries to do conversions while builing
the prepared statement. One if them is checking the columntypes, and
replace the ? with ?::bool in the prepared statement if the columntype
is of boolean type. Another is something similar if the columntype is a
timestamp or date (I haven't tested if that part still works though, I
realize now).

--=20
Eric Veldhuyzen
xs4all NSA team

--f0KYrhQ4vYSV2aJu
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+ex98B/Bwnz56kZERAulEAJ9ReVgAh0nUiFSpN/V9KjPX1z37OwCgldLQ
dmndFKqgLezgYIekGZzeOSo=
=ihtC
-----END PGP SIGNATURE-----

--f0KYrhQ4vYSV2aJu--