Date: Thu, 8 Sep 2005 17:54:35 -0600
From: Michael Fuhr <mike@fuhr.org>
To: =?iso-8859-1?Q?Jo=FFffffffffffe3o?= Carvalho
 <joaocarvalho127@yahoo.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: queries problems
Message-ID: <20050908235435.GA68913@winnie.fuhr.org>
References: <20050902020346.GA58397@winnie.fuhr.org>
	<20050908223348.21917.qmail@web50614.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20050908223348.21917.qmail@web50614.mail.yahoo.com>

On Thu, Sep 08, 2005 at 10:33:48PM +0000, Joÿffffffffffe3o Carvalho wrote:
> The problem here is to compare int2vector with int2.

Presumably you're talking about joining pg_index.indkey against
pg_attribute.attnum -- is that correct?  Will pg_get_indexdef() not
work for your needs?

I don't know if there's an easier way to check if an int2 is a
member of an int2vector, but you could write a function to convert
an int2vector to an int2 array and then use an "= ANY" expression.
The following seems a little ugly, but it works for me in simple
tests:

CREATE FUNCTION int2vector2array(int2vector) RETURNS int2[] AS $$
BEGIN
    RETURN string_to_array(textin(int2vectorout($1)), ' ');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

SELECT 2::smallint = ANY (int2vector2array('1 2 3'));
 ?column? 
----------
 t
(1 row)

SELECT 4::smallint = ANY (int2vector2array('1 2 3'));
 ?column? 
----------
 f
(1 row)

Improvements or other suggestions welcome.

-- 
Michael Fuhr