IN Operator query - Mailing list pgsql-novice

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.