Skip site navigation (1) Skip section navigation (2)

Re: Variable array sizes with PQexecParams

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Garcia, Joshua" <Joshua(dot)Garcia(at)xerox(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Variable array sizes with PQexecParams
Date: 2006-06-26 20:32:24
Message-ID: 22631.1151353944@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Garcia, Joshua" <Joshua(dot)Garcia(at)xerox(dot)com> writes:
> I want to use PQexecParams to insert into a field that takes an array of
> varchars.  I tried something like:
> INSERT INTO table1(column1) VALUES ('{$1,$2}')
> But, this just inserts {$1,$2} into the field.

Well, yeah.  If it did anything else that'd be a catastrophic bug.

Try something like

INSERT INTO table1(column1) VALUES (ARRAY[$1,$2])

For varchar this will probably work as-is, for other datatypes you might
need to add explicit casts to determine the array element types, eg

INSERT INTO table1(column1) VALUES (ARRAY[$1::integer,$2::integer])

Another possibility is to treat the whole array value as one parameter:

INSERT INTO table1(column1) VALUES ($1)

where the value of $1 is like "{foo,bar}", but this gets into having to
quote the data values correctly to make them valid array elements.  The
first way is probably safer.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Garcia, JoshuaDate: 2006-06-26 20:39:56
Subject: Re: Variable array sizes with PQexecParams
Previous:From: Richard Broersma JrDate: 2006-06-26 20:21:54
Subject: Re: Table Merge Successful, Primary Keys Missing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group