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