Re: array indizes in SQL - Mailing list pgsql-novice

From Hans-Peter Oeri
Subject Re: array indizes in SQL
Date
Msg-id 473887B6.1060908@oeri.ch
Whole thread Raw
In response to Re: array indizes in SQL  ("Rodrigo De León" <rdeleonp@gmail.com>)
Responses Re: array indizes in SQL  ("Rodrigo De León" <rdeleonp@gmail.com>)
List pgsql-novice
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



pgsql-novice by date:

Previous
From: "Rodrigo De León"
Date:
Subject: Re: array indizes in SQL
Next
From: "David Monarchi"
Date:
Subject: Constructing two-dimensional arrays from a query