Thread: 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 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

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:
> William Garrison <postgres@mobydisk.com> writes:
>
>>     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
>
> You meant array or bytea?
>
>
> neo=# select '{1, 2, 3}'::int[];
>   int4
> ---------
>  {1,2,3}
> (1 record)
>
> neo=#
>
>