Обсуждение: array indizes in SQL

Hi!

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

As you see, I'm not really satisfied. Although a pk with more than five
fields surely is rare (and bad design), I would like my query to be
"clean" - even if some stranger would create a six field pk. (problem
applies not only to pks, of course)

Is there a way to adapt the series maximum to the actual array size - at
least get an upper bound?
Is there a better way to "decompose" an array? (that I didn't find)

Thanks
HPO

On 11/12/07, Hans-Peter Oeri <hp@oeri.ch> wrote:

...

> 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

...

> Is there a better way to "decompose" an array? (that I didn't find)

SELECT a.attname AS "primarykey"
FROM pg_catalog.pg_attribute AS a,
     pg_catalog.pg_constraint AS o
WHERE a.attnum = ANY (o.conkey)
AND   a.attrelid = o.conrelid
AND   o.contype = 'p'
AND   o.conrelid = CAST ( ? AS regclass)
ORDER BY a.attnum ASC

Hi!

Rodrigo De León wrote:

Is there a better way to "decompose" an array? (that I didn't find)   

SELECT a.attname AS "primarykey"
FROM pg_catalog.pg_attribute AS a,    pg_catalog.pg_constraint AS o
WHERE a.attnum = ANY (o.conkey)
AND   a.attrelid = o.conrelid
AND   o.contype = 'p'
AND   o.conrelid = CAST ( ? AS regclass)
ORDER BY a.attnum ASC 

Thanks for your input... However, you're ignoring the index field order - which might differ from attribute order. At least some querys need 'correct' index orders, like foreign key (field) relations: the field at index position 2 in table a relates to the field at index position 2 in table b.

HPO

On 11/12/07, Hans-Peter Oeri <hp@oeri.ch> wrote:
> Thanks for your input... However, you're ignoring the index field order
> - which might differ from attribute order. At least some querys need
> 'correct' index orders, like foreign key (field) relations: the field at
> index position 2 in table a relates to the field at index position 2 in
> table b.

Can you give details on what you're doing that depends on such ordering?

Just to have a better background of your requirements.

Hi!

Rodrigo De León wrote:
> Can you give details on what you're doing that depends on such ordering?
> Just to have a better background of your requirements
Of course... One of my (slightly longer) queries should return all
tables having a fk to myself, including the field pairings:

SELECT
    CAST(c.conrelid as regclass) AS "you"
  , am.attname AS "mine"
  , ay.attname AS "yours"
FROM
    generate_series(1,5) as i
  , pg_catalog.pg_attribute as am
  , pg_catalog.pg_attribute as ay
  , pg_catalog.pg_constraint as c
WHERE
      c.confrelid  = CAST(? AS regclass)
  AND am.attrelid  = c.confrelid
  AND ay.attrelid  = c.conrelid
  AND c.confkey[i] = am.attnum           -- position in the respective array
  AND c.conkey[i]  = ay.attnum           -- must be identical
ORDER BY
  i ASC

As far as I have seen, there is no "give me the position of value X in
array Y" function...

HPO

On Nov 12, 2007 12:04 PM, Hans-Peter Oeri <hp@oeri.ch> wrote:
> SELECT
>     CAST(c.conrelid as regclass) AS "you"
>   , am.attname AS "mine"
>   , ay.attname AS "yours"
> FROM
>     generate_series(1,5) as i
>   , pg_catalog.pg_attribute as am
>   , pg_catalog.pg_attribute as ay
>   , pg_catalog.pg_constraint as c
> WHERE
>       c.confrelid  = CAST(? AS regclass)
>   AND am.attrelid  = c.confrelid
>   AND ay.attrelid  = c.conrelid
>   AND c.confkey[i] = am.attnum           -- position in the respective array
>   AND c.conkey[i]  = ay.attnum           -- must be identical
> ORDER BY
>   i ASC

I see. If correct ordering is a requirement, I see no reason to
classify your original query as "unclean". If it works for you, and
it's fast enough, go for it.

Good luck.

Hans-Peter Oeri <hp@oeri.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