Обсуждение: 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=# > >