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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Porter | 2007-11-15 02:30:48 | Re: populating arrays with default values |
Previous Message | Rodrigo De León | 2007-11-14 21:16:49 | Re: array indizes in SQL |