How can I pass an array to SPI_execute_with_args()? - Mailing list pgsql-general

Hi,

I would like to execute the code below.

I SELECTed a bigint[] from the database into "Datum ids",
I need to insert a new bigint ID in the middle.

                Datum                   ids;
                int                     n_ids;
                int                     idx_min, idx_max, idx_mid;
                ArrayType          *ids_arr;
                Datum              *ids_data;
                ArrayType               *array_prefix, *array_postfix;

                ...
                ids = SPI_getbinval(prod_inv->vals[0],
prod_inv->tupdesc, 1, &isnull);
                n_ids = DatumGetInt32(DirectFunctionCall2(array_length,
ids, Int32GetDatum(1)));
                ids_arr = DatumGetArrayTypeP(ids);
                ids_data = (Datum *) ARR_DATA_PTR(ids_arr);
                ...

At this point it's already ensured that 0 < idx_min < n_ids - 1,
idx_min is the index in the array where I need to split:

                get_typlenbyvalalign(INT8OID, &typlen, &typbyval,
&typalign);

                /* Split the array and UPDATE */
                /* ids[0 ... idx_min - 1] || new_id || ids[idx_min ...
n_ids - 1] */
                array_prefix = construct_array(ids_data, idx_min,
                                        INT8OID, typlen, typbyval,
typalign);
                array_postfix = construct_array(&ids_data[idx_min],
n_ids - idx_min,
                                        INT8OID, typlen, typbyval,
typalign);

                oids[0] = ANYARRAYOID;
                values[0] = PointerGetDatum(array_prefix);
                nulls[0] = false;

 >>>>>>>>       oids[1] = INT8OID; /* ANYELEMENTOID; */
                values[1] = id; /* really an int8 Datum */
                nulls[1] = false;

                oids[2] = ANYARRAYOID;
                values[2] = PointerGetDatum(array_postfix);
                nulls[2] = false;

                oids[3] = TEXTOID;
                values[3] = lex;
                nulls[3] = false;

                ret = SPI_execute_with_args(
                                "UPDATE product.t_product_inv SET ids =
array_append($1, $2) || $3 WHERE word = $4",
                                4, oids, values, nulls, false, 1);

If the above marked line sets oids[1] = INT8OID, I get this error:

ERROR:  function array_append(anyarray, bigint) does not exist
LINE 1: UPDATE product.t_product_inv SET ids = array_append($1, $2) ...
                                               ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:  UPDATE product.t_product_inv SET ids = array_append($1, $2) ||
$3 WHERE word = $4

If I use ANYELEMENTOID there, I get this error:

ERROR:  argument declared "anyarray" is not an array but type anyarray
CONTEXT:  SQL statement "UPDATE product.t_product_inv SET ids =
array_append($1, $2) || $3 WHERE word = $4"

I am stuck here. Can someone help me?

Thanks in advance,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/