From: | DrYSG <ygutfreund(at)draper(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | IN Operator query |
Date: | 2012-07-05 14:31:58 |
Message-ID: | 1341498718622-5715470.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-07-05 14:44:37 | Re: IN Operator query |
Previous Message | Lasma Sietinsone | 2012-07-03 10:55:58 | PLDOC for PostgreSQL? |