Postgres Pro
Downloads
Home   >   mailing lists

Thread: prepared statements

prepared statements

From
Scott Frankel
Date:
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

Re: prepared statements

From
"Daniel Verite"
Date:
    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

Re: prepared statements

From
Scott Frankel
Date:
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:

>     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
>

Re: prepared statements

From
Alban Hertroys
Date:
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-preparedinsert. 


You would have needed it for an unprepared statement just as well in this case. You expect the planner to guess which
columnsyou left out? ;) 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c4ac805286212004583493!