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

Re: array indizes in SQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Hans-Peter Oeri <hp(at)oeri(dot)ch>
Cc: ListaPostgre <pgsql-novice(at)postgresql(dot)org>
Subject: Re: array indizes in SQL
Date: 2007-11-14 23:03:42
Message-ID: 17841.1195081422@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Hans-Peter Oeri <hp(at)oeri(dot)ch> writes:
> I flee arrays in SQL whenever I can... however, to programmatically get
> index fields from the system catalog, there I am. :(

> E.g. to get the pk fields I wrote:

> SELECT
>   a.attname AS "primarykey"
> FROM
>   generate_series(1,5) as i,                -- yuck!!
>   pg_catalog.pg_attribute as a,
>   pg_catalog.pg_constraint as o
> WHERE
>   o.conkey[i] = a.attnum
>   AND a.attrelid = o.conrelid
>   AND o.contype = 'p'
>   AND o.conrelid = CAST( ? AS regclass )
> ORDER BY
>   i ASC

[ sorry for not responding sooner ]

Did you look into the information_schema views to see how this has been
dealt with before?  Those views rely on this set-returning function:

/* Expand any 1-D array into a set with integers 1..N */
CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
    RETURNS SETOF RECORD
    LANGUAGE sql STRICT IMMUTABLE
    AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
        from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
                                        pg_catalog.array_upper($1,1),
                                        1) as g(s)';

Your problem would work out like this:

SELECT
  a.attname AS "primarykey"
FROM
  pg_catalog.pg_attribute as a,
  (SELECT conrelid, information_schema._pg_expandarray(conkey) as k
   FROM
     pg_catalog.pg_constraint as o
   WHERE
     o.contype = 'p'
     AND o.conrelid = CAST( 'foo' AS regclass )
  ) ss
WHERE
  a.attrelid = ss.conrelid
  AND a.attnum = (ss.k).x
ORDER BY
  (ss.k).n ASC
;

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Kenneth PorterDate: 2007-11-15 02:30:48
Subject: Re: populating arrays with default values
Previous:From: Rodrigo De LeónDate: 2007-11-14 21:16:49
Subject: Re: array indizes in SQL

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