From: Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
To: pgsql-sql@postgresql.org
Cc: Lane Van Ingen <lvaningen@esncc.com>
Subject: Re: SQL CASE Statements
Date: Mon, 22 Aug 2005 18:47:48 -0300 (ART)
Message-ID: <20050822214748.13623.qmail@web52703.mail.yahoo.com> (raw)
In-Reply-To: <EKEMKEFLOMKDDLIALABIMEKFCBAA.lvaningen@esncc.com>
Dear Lane, is that what you want?
CREATE TABLE network_nodes (
node_id SERIAL PRIMARY KEY,
node_name VARCHAR,
default_gateway_interface_id INTEGER
);
CREATE TABLE router_interfaces (
interface_id SERIAL PRIMARY KEY,
node_id INT REFERENCES network_nodes
);
CREATE VIEW current_default_gateways_v (router_id, default_gateway) AS
SELECT interface_id,
CASE WHEN interface_id IN
(SELECT interface_id
FROM router_interfaces ri, network_nodes nn
WHERE ri.node_id = nn.node_id
AND ri.interface_id = nn.default_gateway_interface_id)
THEN 1
ELSE 0
END AS if_default_gateway
FROM router_interfaces;
INSERT INTO network_nodes VALUES(DEFAULT, 'node1',1);
INSERT INTO network_nodes VALUES(DEFAULT, 'node2',2);
INSERT INTO network_nodes VALUES(DEFAULT, 'node3',3);
INSERT INTO network_nodes VALUES(DEFAULT, 'node4',4);
INSERT INTO router_interfaces VALUES(DEFAULT,1);
INSERT INTO router_interfaces VALUES(DEFAULT,2);
INSERT INTO router_interfaces VALUES(DEFAULT,2);
INSERT INTO router_interfaces VALUES(DEFAULT,1);
SELECT * FROM network_nodes;
SELECT * FROM router_interfaces;
SELECT * FROM current_default_gateways_v;
teste=> SELECT * FROM network_nodes;
node_id | node_name | default_gateway_interface_id
---------+-----------+------------------------------
1 | node1 | 1
2 | node2 | 2
3 | node3 | 3
4 | node4 | 4
(4 rows)
teste=> SELECT * FROM router_interfaces;
interface_id | node_id
--------------+---------
1 | 1
2 | 2
3 | 2
4 | 1
(4 rows)
teste=> SELECT * FROM current_default_gateways_v;
router_id | default_gateway
-----------+-----------------
1 | 1
2 | 1
3 | 0
4 | 0
(4 rows)
--- Lane Van Ingen <lvaningen@esncc.com> escreveu:
> Halley, here is a sample for you that might help; the purpose of this
> function was to set an indicator of '1' or '0' (true or false) on a router
> interface if the router interface ID was the same as the default gateway for
> the Router node ID:
>
> create view current_default_gateways_v (router_id, default_gateway) AS
> select router_id,
> case
> when router_id in (select interface_id from router_interface ri,
> network_nodes nn
> where ri.node_id = nn.node_id
> and ri.interface_id = nn.default_gateway_interface_id)
> then 1
> else 0
> end as if_default_gateway
> from router_interface;
>
> TABLES USED:
> network_nodes:
> node_id, serial
> node_name, varchar
> default_gateway_interface_id, integer
>
> router_interfaces:
> interface_id, serial (integer)
> node_id (FK)
>
__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/
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: halleypo@yahoo.com.br, lvaningen@esncc.com
Subject: Re: SQL CASE Statements
In-Reply-To: <20050822214748.13623.qmail@web52703.mail.yahoo.com>
* 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