Re: Array with Subselect / ANY - cast?

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