Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: PREPARE statement example error.
Date: Fri, 29 Oct 2004 11:37:21 -0700
Message-ID: <727B82C682D8E8409BD807B361874F4102208FD3@xcgmd050.northgrum.com>
Thread-Topic: PREPARE statement example error.
Thread-Index: AcS95lT2ERbcBzOmSB2AdcN2HU7pzg==
From: "Wood, Bruce" <Bruce.Wood@ngc.com>
To: "PostgreSQL Docs" <pgsql-docs@postgresql.org>

There is an error in your example for the prepare statement on page =
http://developer.postgresql.org/docs/postgres/sql-prepare.html.

Using the example from the link above

rainstorm=3D> PREPARE fooplan (int, text, bool, numeric(8,2)) AS
rainstorm->     INSERT INTO foo VALUES($1, '$2', '$3', '$4');
ERROR:  invalid input syntax for type boolean: "$3"

Taking out the single quotes around the boolean parameter 3 yields

rainstorm=3D> PREPARE fooplan (int, text, bool, numeric(8,2)) AS
rainstorm->     INSERT INTO foo VALUES($1, '$2', $3, '$4');
ERROR:  invalid input syntax for type real: "$4"

So take out the single quotes around the numeric parameter 4 yields

rainstorm=3D> PREPARE fooplan (int, text, bool, numeric(8,2)) AS
rainstorm->     INSERT INTO foo VALUES($1, '$2', $3, $4);
PREPARE
rainstorm=3D> EXECUTE fooplan(1, 'Hunter Valley', 't', '200.00');
INSERT 0 1

So that "works", but what it does is

rainstorm=3D> select * from foo;
 key | address | valid | cost
-----+---------+-------+------
   1 | $2      | t     |  200
(1 row)

It inserts the literal string of parameter two rather than the value =
provided for parameter 2.  This is in the PostgreSQL 8 beta 4 Windows =
native version.

Bruce Wood			Reception (301) 373-2360 =20
Northrop Grumman PRB Systems	Voice Mail (301) 373-2388 ext 2151
43865 Airport View Drive		Fax           (301) 373-2398
Hollywood, MD 20636		Email         bruce.wood@ngc.com