Обсуждение: IN Operator query
I made a naive and stupid assumption that I could pass in a TEXT parameter to a plpsql Stored Procedure, and use that value in a IN SQL operation. That is My naïve hope was that if iFILTER was set to: "CADRG, DTED1, DTED2, SRTF" (cat.type in (iFilter)) would expand to: (cat.type in (CADRG, DTED1, DTED2, SRTF)) But that is not working. CREATE OR REPLACE FUNCTION portal.search_catalog(searchbox text, inside boolean, startdate timestamp without time zone, enddate timestamp without time zone, *ifilter text*, maxitems integer) RETURNS refcursor AS $BODY$ DECLARE ref refcursor; BEGIN IF (inside) THEN OPEN ref FOR SELECT cat.idx, cat.size_bytes, cat.date, cat.type, cat.elevation, cat.source, cat.egpl_date, cat.classification, cat.classification_int, cat.handling, cat.originator, cat.datum, cat.product_id, cat.product, cat.description, cat.path, cat.bbox FROM portal.catalog AS cat WHERE public.st_contains(public.st_geomfromtext(searchBox, 4326) , cat.poly) AND (cat.date >= startDate AND cat.date <= endDate) AND * (cat.type in (iFilter))* LIMIT maxItems; ELSE OPEN ref FOR SELECT cat.idx, cat.size_bytes, cat.date, cat.type, cat.elevation, cat.source, cat.egpl_date, cat.classification, cat.classification_int, cat.handling, cat.originator, cat.datum, cat.product_id, cat.product, cat.description, cat.path, cat.bbox FROM portal.catalog AS cat WHERE public.st_intersects(public.st_geomfromtext(searchBox, 4326) , cat.poly) AND (cat.date >= startDate AND cat.date <= endDate) AND * (cat.type in (iFilter))* LIMIT maxItems; END IF; RETURN ref; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION portal.search_catalog(text, boolean, timestamp without time zone, timestamp without time zone, text, integer) OWNER TO postgres; -- View this message in context: http://postgresql.1045698.n5.nabble.com/IN-Operator-query-tp5715470.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
DrYSG <ygutfreund@draper.com> writes: > My naïve hope was that if iFILTER was set to: "CADRG, DTED1, DTED2, SRTF" > (cat.type in (iFilter)) would expand to: > (cat.type in (CADRG, DTED1, DTED2, SRTF)) > But that is not working. Nope, it will not (and a good thing too IMO --- would you really want commas in text strings to be dangerous?) You might be able to do what you need by passing the argument as a text array and using "cat.type = any(ifilter)". regards, tom lane
On Thu, Jul 5, 2012 at 10:31 AM, DrYSG <ygutfreund@draper.com> wrote: > I made a naive and stupid assumption that I could pass in a TEXT parameter to > a plpsql Stored Procedure, and use that value in a IN SQL operation. > > That is > > My naïve hope was that if iFILTER was set to: "CADRG, DTED1, DTED2, SRTF" > > (cat.type in (iFilter)) would expand to: > > (cat.type in (CADRG, DTED1, DTED2, SRTF)) > > But that is not working. > I believe using the PostgreSQL array type might help, http://www.postgresql.org/docs/9.1/static/arrays.html Instead of declaring your iFilter parameter as TEXT, declare it as ARRAY. Then instead of 'CADRG, DTED1, DTED2, SRTF' you would put the input parameter as '{"CADRG", "DTED1", "DTED2", "SRTF"}' And then change cat.type in (iFilter) to cat.type = ANY (iFilter) Good luck. -Mike Swierczek
On Thu, Jul 5, 2012 at 10:48 AM, Michael Swierczek <mike.swierczek@gmail.com> wrote: > On Thu, Jul 5, 2012 at 10:31 AM, DrYSG <ygutfreund@draper.com> wrote: >> I made a naive and stupid assumption that I could pass in a TEXT parameter to >> a plpsql Stored Procedure, and use that value in a IN SQL operation. >> >> That is >> >> My naïve hope was that if iFILTER was set to: "CADRG, DTED1, DTED2, SRTF" >> >> (cat.type in (iFilter)) would expand to: >> >> (cat.type in (CADRG, DTED1, DTED2, SRTF)) >> >> But that is not working. >> > > I believe using the PostgreSQL array type might help, > http://www.postgresql.org/docs/9.1/static/arrays.html > > Instead of declaring your iFilter parameter as TEXT, declare it as ARRAY. Whoops! For Array declarations, it's not literally "ARRAY", it would be "TEXT[]" (or "text[]"). I'm sorry for that minor oversite. -Mike > Then instead of 'CADRG, DTED1, DTED2, SRTF' you would put the input > parameter as '{"CADRG", "DTED1", "DTED2", "SRTF"}' > > And then change cat.type in (iFilter) to cat.type = ANY (iFilter) > > Good luck. > -Mike Swierczek