Thread: Finding a value in an array field

How can I know if a value is into an array field?

something like:

  SELECT field1, field2 FROM myTableName
  WHERE 10 in myArrayField

PS: I do NOT know then number of array elements!

tia

Roberto de Amorim - +55 48 346-2243
Software engineer at SmartBit Software
Delphi and Interbase consultant

I think you need to look at the functions in /contrib. I don't think you can
do it just in SQL.

Regards,
    Jeff Davis

On Thursday 17 October 2002 05:45 pm, Roberto (SmartBit) wrote:
> How can I know if a value is into an array field?
>
> something like:
>
>   SELECT field1, field2 FROM myTableName
>   WHERE 10 in myArrayField
>
> PS: I do NOT know then number of array elements!
>
> tia
>
> Roberto de Amorim - +55 48 346-2243
> Software engineer at SmartBit Software
> Delphi and Interbase consultant
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

thanks Jeff

this is my SQL command:

select pg_attribute.attname from pg_index, pg_attribute, pg_class
where indrelid=pg_class.oid and
      (indrelid=pg_attribute.attrelid and pg_attribute.attnum IN indkey) and
      pg_class='mytablename' and idxisprimary


I'm trying to return fields of MyTable's primary key!!

the field 'indkey' is an array!!

if thera are other ways... please, tell me how..

tia

----- Original Message -----
From: "Jeff Davis" <list-pgsql-general@empires.org>
To: <pgsql-general@postgresql.org>
Sent: Thursday, October 17, 2002 9:08 PM
Subject: Re: [GENERAL] Finding a value in an array field



I think you need to look at the functions in /contrib. I don't think you can
do it just in SQL.

Regards,
Jeff Davis

On Thursday 17 October 2002 05:45 pm, Roberto (SmartBit) wrote:
> How can I know if a value is into an array field?
>
> something like:
>
>   SELECT field1, field2 FROM myTableName
>   WHERE 10 in myArrayField
>
> PS: I do NOT know then number of array elements!
>
> tia
>
> Roberto de Amorim - +55 48 346-2243
> Software engineer at SmartBit Software
> Delphi and Interbase consultant
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Hi all

how could I do a single select resulting a list of field name that are
primary keys of a table??

please, help me

tia

Roberto Amorim

SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'rms_users' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid
AND i.indisprimary AND i.indisunique ORDER BY c2.relname

Robert Treat

On Fri, 2002-10-18 at 12:37, Roberto (SmartBit) wrote:
> Hi all
>
> how could I do a single select resulting a list of field name that are
> primary keys of a table??
>
> please, help me
>
> tia
>
> Roberto Amorim
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Roberto (SmartBit) wrote:
> Hi all
>
> how could I do a single select resulting a list of field name that are
> primary keys of a table??
>

In PostgreSQL 7.2.x (and I think in 7.1.x) you can use the plpgsql function
get_pk() located here:

http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=36

-----------------------------------------------------------------
-- Function: get_pk
-- Purpose:  Retrieves a comma delimited
--           list of attribute names
--           making up the primary key
--           of the relation passed as argument $1


In 7.3 (now in beta), you can use dblink_get_pkey() from contrib/dblink.
Here's how it looks:

create table foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for
table 'foo'

-- list the primary key fields
select * from dblink_get_pkey('foo');
  position | colname
----------+---------
         1 | f1
         2 | f2
(2 rows)


Joe