Обсуждение: Variable array sizes with PQexecParams

"Garcia, Joshua" <Joshua.Garcia@xerox.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

(Sorry I sent this to you twice, Tom.  I forgot to CC to the
pgsql-novice list.)

Thanks a lot, Tom.

One follow-up question:
If I want the number of elements in that array to vary with each
execution, then I'd have to use the $1="'{foo,bar}'" method right?
Then, I'd construct that string based on the number of elements I have
for that execution.

Josh

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, June 26, 2006 1:32 PM
To: Garcia, Joshua
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Variable array sizes with PQexecParams

"Garcia, Joshua" <Joshua.Garcia@xerox.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

"Garcia, Joshua" <Joshua.Garcia@xerox.com> writes:
> If I want the number of elements in that array to vary with each
> execution, then I'd have to use the $1="'{foo,bar}'" method right?

AFAICS you could do it either way --- the pain about balances out,
once you consider the effort involved in doing the quoting correctly.

            regards, tom lane