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

Re: array indizes in SQL

From: Hans-Peter Oeri <hp(at)oeri(dot)ch>
To: Rodrigo De León <rdeleonp(at)gmail(dot)com>
Cc: ListaPostgre <pgsql-novice(at)postgresql(dot)org>
Subject: Re: array indizes in SQL
Date: 2007-11-12 17:04:54
Message-ID: 473887B6.1060908@oeri.ch (view raw or flat)
Thread:
Lists: 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



In response to

Responses

pgsql-novice by date

Next:From: David MonarchiDate: 2007-11-12 18:05:35
Subject: Constructing two-dimensional arrays from a query
Previous:From: Rodrigo De LeónDate: 2007-11-12 16:40:29
Subject: Re: array indizes in SQL

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