Thread: Variable array sizes with PQexecParams
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.
However, I also want to be able to select at runtime the number of elements I’ll be inserting into the field. For example, rather than having two elements in the array as I tried above, I just want one element as shown below:
INSERT INTO table1(column1) VALUES ($1)
How can I do all this?
Thanks in advance,
Josh
"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