On Aug 21, 2007, at 12:49 , Josh Trutwin wrote:
SELECT pb_ids FROM pb WHERE id = 123:
pb_id
-----------------------
{196,213,215,229,409}
These numbers map to a productid in tblproducts so I figured I could
do this:
SELECT *
FROM tblproducts
WHERE productid = ANY (
SELECT pb_ids FROM pb WHERE id=123
);
Out of curiosity, what led to the schema design of storing these
pb_id values in an array rather than in a many-to-many table? You're
working against the database server here. The usual way to define
this relationship would be
CREATE TABLE pb (id INTEGER PRIMARY KEY);
CREATE TABLE pb_ids
(
id INTEGER NOT NULL REFERENCES pb
, pb_id INTEGER NOT NULL
REFERENCES tblproducts (pb)
, PRIMARY KEY (id, pb)
);
(if I've interpreted the column and table names correctly)
Then your query reduces to a simple
SELECT *
FROM tblproducts
JOIN pb_ids ON (pb_id = pb)
WHERE id = 123;
This reduces the query to straight-forward SQL (which is set based)
rather than wrangling arrays (which are really better considered
opaque from the standpoint of database schema design) and enables
referential integrity using built-in foreign key constraints rather
than requiring custom triggers (to make sure each element of the
pb_id array corresponds to a pb value in tblproducts).
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
