StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to create an "on-the-fly" mapping table within a SELECT statement in Postgresql
text
Body
copied!<p>I'm creating a select statement that combines two tables, <code>zone</code> and <code>output</code>, based on a referenced <code>device</code> table and on a mapping of <code>zone_number</code> to <code>output_type_id</code>. The mapping of <code>zone_number</code> to <code>output_type_id</code> doesn't appear anywhere in the database, and I would like to create it "on-the-fly" within the select statement. Below is my schema: </p> <pre><code>CREATE TABLE output_type ( id INTEGER NOT NULL, name TEXT, PRIMARY KEY (id) ); CREATE TABLE device ( id INTEGER NOT NULL, name TEXT, PRIMARY KEY (id) ); CREATE TABLE zone ( id SERIAL NOT NULL, device_id INTEGER NOT NULL REFERENCES device(id), zone_number INTEGER NOT NULL, PRIMARY KEY (id), UNIQUE (zone_number) ); CREATE TABLE output ( id SERIAL NOT NULL, device_id INTEGER NOT NULL REFERENCES device(id), output_type_id INTEGER NOT NULL REFERENCES output_type(id), enabled BOOLEAN NOT NULL, PRIMARY KEY (id) ); </code></pre> <p>And here is some example data:</p> <pre><code>INSERT INTO output_type (id, name) VALUES (101, 'Output 1'), (202, 'Output 2'), (303, 'Output 3'), (404, 'Output 4'); INSERT INTO device (id, name) VALUES (1, 'Test Device'); INSERT INTO zone (device_id, zone_number) VALUES (1, 1), (1, 2), (1, 3), (1, 4); INSERT INTO output (device_id, output_type_id, enabled) VALUES (1, 101, TRUE), (1, 202, FALSE), (1, 303, FALSE), (1, 404, TRUE); </code></pre> <p>I need to get the associated <code>enabled</code> field from the output table for each zone for a given device. Each <code>zone_number</code> maps to an <code>output_type_id</code>. For this example: </p> <pre><code>zone_number | output_type_id ---------------------------- 1 | 101 2 | 202 3 | 303 4 | 404 </code></pre> <p>One way to handle the mapping would be to create a new table</p> <pre><code>CREATE TABLE zone_output_type_map ( zone_number INTEGER, output_type_id INTEGER NOT NULL REFERENCES output_type(id) ); INSERT INTO zone_output_type_map (zone_number, output_type_id) VALUES (1, 101), (2, 202), (3, 303), (4, 404); </code></pre> <p>And use the following SQL to get all zones, plus the <code>enabled</code> flag, for device 1: </p> <pre><code>SELECT zone.*, output.enabled FROM zone JOIN output ON output.device_id = zone.device_id JOIN zone_output_type_map map ON map.zone_number = zone.zone_number AND map.output_type_id = output.output_type_id AND zone.device_id = 1 </code></pre> <p>However, I'm looking for a way to create the mapping of zone nunbers to output types without creating a new table and without piecing together a bunch of AND/OR statements. Is there an elegant way to create a mapping between the two fields within the select statement? Something like: </p> <pre><code>SELECT zone.*, output.enabled FROM zone JOIN output ON output.device_id = zone.device_id JOIN ( SELECT ( 1 => 101, 2 => 202, 3 => 303, 4 => 404 ) (zone_number, output_type_id) ) as map ON map.zone_number = zone.zone_number AND map.output_type_id = output.output_type_id AND zone.device_id = 1 </code></pre> <p>Disclaimer: I know that ideally the <code>enabled</code> field would exist in the <code>zone</code> table. However, I don't have control over that piece. I'm just looking for the most elegant solution from the application side. Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload