Re: queries problems

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