Skip site navigation (1) Skip section navigation (2)

Re: IN Operator query

From: Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com>
To: DrYSG <ygutfreund(at)draper(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: IN Operator query
Date: 2012-07-05 14:49:12
Message-ID: CAHp1f1N6XxLytTqx_WhSB4jO=5SMWwkEdYOwcuoTRb8HycELOQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Thu, Jul 5, 2012 at 10:48 AM, Michael Swierczek
<mike(dot)swierczek(at)gmail(dot)com> wrote:
> On Thu, Jul 5, 2012 at 10:31 AM, DrYSG <ygutfreund(at)draper(dot)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

In response to

pgsql-novice by date

Next:From: Andy HalsallDate: 2012-07-06 10:34:06
Subject: Re: Function Scan costs
Previous:From: Michael SwierczekDate: 2012-07-05 14:48:07
Subject: Re: IN Operator query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group