Passing arrays to stored procedures
I have a stored procedure that takes a list of IDs and uses the ANY
operator:
CREATE OR REPLACE FUNCTION CalculateTotals(
customerList bytea[],
out total bigint,
out most_recent_login_date date)
AS $$
BEGIN
SELECT
SUM(totalsize), MAX(last_login)
INTO
$2,$3
FROM
customer
WHERE
customerid = ANY($1);
END;
$$ LANGUAGE 'plpgsql' STABLE;
I'm using npgsql and C#, and I've realized it doesn't support passing
arrays. Barring things like updating npgsql, what form of hackiness
would work best here?
The customerIDs are GUIDs represented as 16-byte arrays. I can pass
them as encoded strings separated by commas or some such silliness. But
I don't see a nice clean split() function that returns me an array. :-(
I'm trying to find some way to pass a postgres array constructor syntax
and have that evaluated, like ARRAY['binary':bytea,...,...] or
{...,...,...} something like that.
Does anyone have any suggestions?
William Garrison <postgres@mobydisk.com> writes:
I'm using npgsql and C#, and I've realized it doesn't support passing
arrays. Barring things like updating npgsql, what form of hackiness
would work best here?
The customerIDs are GUIDs represented as 16-byte arrays. I can pass
them as encoded strings separated by commas or some such silliness. But
I don't see a nice clean split() function that returns me an array. :-(
Do you need one? Can't you just pass the array as one parameter?
The text form of an array is like
{value,value,value}
which shouldn't be that hard to deal with if you can generate the text
form of the individual bytea values.
regards, tom lane
Tom Lane wrote:
William Garrison <postgres@mobydisk.com> writes:
I'm using npgsql and C#, and I've realized it doesn't support passing
arrays. Barring things like updating npgsql, what form of hackiness
would work best here?The customerIDs are GUIDs represented as 16-byte arrays. I can pass
them as encoded strings separated by commas or some such silliness. But
I don't see a nice clean split() function that returns me an array. :-(Do you need one? Can't you just pass the array as one parameter?
The text form of an array is like
{value,value,value}
which shouldn't be that hard to deal with if you can generate the text
form of the individual bytea values.regards, tom lane
That doesn't work. If I pass a string, then it is a string.
CREATE OR REPLACE FUNCTION CalculateTotals(
customerList character varying,
.
.
.
WHERE customerid = ANY($1);
Results in the error:
ERROR: op ANY/ALL (array) requires array on right side
I tried casting the character string to an array afterward:
WHERE customerid = ANY($1::bytea);
which results in:
ERROR: cannot cast type character varying to bytea
William Garrison <postgres@mobydisk.com> writes:
WHERE customerid = ANY($1);
Results in the error:
ERROR: op ANY/ALL (array) requires array on right sideI tried casting the character string to an array afterward:
WHERE customerid = ANY($1::bytea);
which results in:
ERROR: cannot cast type character varying to bytea
You meant array or bytea?
neo=# select '{1, 2, 3}'::int[];
int4
---------
{1,2,3}
(1 record)
neo=#
--
Jorge Godoy <jgodoy@gmail.com>
Yeah, I meant bytea[].
It still doesn't work. Same error:
ERROR: cannot cast type character varying to bytea[]
Not that this is not the same as doing
select '{1, 2, 3}'::int[].
This is the equivalent of doing
SELECT ('{10000, 10000, 10000, 10000}'::varchar(255))::int[];
I can't pass in a string then cast it to something other than a string.
You have made me realize though, that what you are suggesting might work
if I call this as an ad-hoc SELECT statement instead of calling the
stored procedure directly. Less efficient, but then I should be able to
do what you suggest. I'll have to try that. Or maybe that is what you
meant all along.
Jorge Godoy wrote:
Show quoted text
William Garrison <postgres@mobydisk.com> writes:
WHERE customerid = ANY($1);
Results in the error:
ERROR: op ANY/ALL (array) requires array on right sideI tried casting the character string to an array afterward:
WHERE customerid = ANY($1::bytea);
which results in:
ERROR: cannot cast type character varying to byteaYou meant array or bytea?
neo=# select '{1, 2, 3}'::int[];
int4
---------
{1,2,3}
(1 record)neo=#