pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: James Carrier <james.carrier@bulletonline.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Help - multi values
Date: Sun, 10 Feb 2002 15:33:33 -0500
Message-ID: <20020210203333.GA22011@xyzzy.dhs.org> (raw)
In-Reply-To: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk>
References: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk>

  Since you've only given us a vague description to work with, I can only 
give you a vague answer in return.

  For situations like what you've described above, I tend to use the 
following schema:

CREATE TABLE widget ( 
	widget_id SERIAL PRIMARY KEY,
	some_data TEXT 
);

CREATE TABLE category (
	category_id SERIAL PRIMARY KEY,
	name TEXT
);

CREATE TABLE w_x_c (
	widget_id INTEGER NOT NULL REFERENCES widget,
	category_id INTEGER NOT NULL REFERENCES category,
	PRIMARY KEY (category_id, widget_id)
);

-- primary key will implicitly create index good for mapping categories
-- to widgets, and here's an index to go the other way
CREATE INDEX w_x_c_rev_idx ON w_x_c (wigdet_id, category_id);

-- Which categories a widget belongs to (naturally you'd use a subselect
-- or whatever instead of 1)
SELECT * FROM category
NATURAL JOIN
SELECT * FROM w_x_c WHERE widget_id = 1;

-- Since you'll be doing this in the context of a web multi select,
-- You probably want a list of all the categories, one per row, with
-- a column (widget_id) that is either NULL or a number to tell you 
-- if that row is selected or not.
SELECT * FROM category
NATURAL LEFT OUTER JOIN
SELECT widget_id FROM  w_x_c WHERE widget_id = 1;

-- Of course that gives you a row for each category, which is mighty
-- handy if you're doing web work.  But it sounds like you'd prefer
-- things in a different format:

CREATE FUNCTION fugly_concat_step (text, text) RETURNS text 
AS 'SELECT ($1 || $2 || ''|'')::text;' LANGUAGE 'sql';

CREATE AGGREGATE fugly_concat ( 
	BASETYPE = text, STYPE = text,
	SFUNC = fugly_concat_step,
	INITCOND = '|'
);
            

SELECT widget_id fugly_concat(category_id::text) 
FROM w_x_c WHERE widget_id = 1
GROUP BY widget_id;

  Now, when you want to add a new category, simply insert it into the 
category table.  Takes a little extra PHP coding, but you won't ever
have to update your code because you've added or changed categories.

-- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

view thread (7+ messages)  latest in thread

Message-ID: <20020210203333.GA22011@xyzzy.dhs.org>
Permalink:  ../20020210203333.GA22011@xyzzy.dhs.org/
Also on:    postgresql.org/message-id/20020210203333.GA22011@xyzzy.dhs.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: drew@xyzzy.dhs.org, james.carrier@bulletonline.com
  Subject: Re: SQL Help - multi values
  In-Reply-To: <20020210203333.GA22011@xyzzy.dhs.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox