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