prepared statements
Hi all,
I'm working with prepared statements directly in pg for the first time
and have a couple of questions.
Does a prepared statement used to insert into a table need to insert
into all columns of the table? I've found that, for a table with a
serial sequence key as its first column, I have to specify the key in
my prepared statement or I get type errors: ERROR: column "foo_id"
is of type integer but expression is of type character varying.
What's the best way to specify the next value for the serial sequence
key if subqueries are not allowed in a prepared statement's execute
parameter: ERROR: cannot use subquery in EXECUTE parameter
For example, given the following table definition:
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY,
name VARCHAR(32) UNIQUE NOT NULL,
description TEXT,
body TEXT DEFAULT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP,
UNIQUE (name));
What's the best way to insert several records that have lots of
special characters in the "body" column? eg:
PREPARE fooprep (int, VARCHAR(32), text, text) AS
INSERT INTO foo VALUES ($1, $2, $3, $4);
EXECUTE (fooprep
(SELECT nextval('foo_id_seq')),
'foo1',
'this is foo1',
'#!()[]{}
qwepoiasdlkjzxcmnb
/\1\2\3\4\5\6\7\8\9/');
Thanks in advance!
Scott
Scott Frankel wrote:
I've found that, for a table with a
serial sequence key as its first column, I have to specify the key in
my prepared statement or I get type errors: ERROR: column "foo_id"
is of type integer but expression is of type character varying.
Let's try:
test=> create table t(a serial, b int);
NOTICE: CREATE TABLE will create implicit sequence "t_a_seq" for serial
column "t.a"
CREATE TABLE
test=> prepare a as insert into t(b) values($1);
PREPARE
test=> execute a(2);
INSERT 0 1
test=> select * from t;
a | b
---+---
1 | 2
(1 row)
No error here...
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Works! The bug in my example was not passing the INSERT statement an
explicit list of column names, as per any non-prepared insert.
Thanks!
Scott
On Jul 23, 2010, at 2:53 PM, Daniel Verite wrote:
Show quoted text
Scott Frankel wrote:
I've found that, for a table with a
serial sequence key as its first column, I have to specify the key in
my prepared statement or I get type errors: ERROR: column "foo_id"
is of type integer but expression is of type character varying.Let's try:
test=> create table t(a serial, b int);
NOTICE: CREATE TABLE will create implicit sequence "t_a_seq" for
serial
column "t.a"
CREATE TABLE
test=> prepare a as insert into t(b) values($1);
PREPARE
test=> execute a(2);
INSERT 0 1
test=> select * from t;
a | b
---+---
1 | 2
(1 row)No error here...
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 24 Jul 2010, at 1:32, Scott Frankel wrote:
Works! The bug in my example was not passing the INSERT statement an explicit list of column names, as per any non-prepared insert.
You would have needed it for an unprepared statement just as well in this case. You expect the planner to guess which columns you left out? ;)
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c4ac805286212004583493!