StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>CREATE VIEW clueless AS ( SELECT na.a, na.b,na.c , se.d,se.e, se.f , do_something_usefull ( na.g, se.i, 'clue' ) AS usefull FROM nameless na JOIN sense se ON se.clue_id = na.clue_id -- .. maybe more ); </code></pre> <p>UPDATE: after getting the requirements right, this thing <em>can</em> be done using rules.</p> <pre><code>SET search_path='tmp'; -- Create a table and a view on it DROP TABLE nameless CASCADE; CREATE TABLE nameless ( a INTEGER NOT NULL , b INTEGER NOT NULL , PRIMARY KEY (a,b) ); CREATE VIEW clueless AS ( SELECT a, b , a*b AS c FROM nameless ); -- Enter some data INSERT INTO nameless(a,b) SELECT a, b FROM generate_series(1,5) a , generate_series(11,12) b ; CREATE RULE clue_i AS ON INSERT TO clueless DO INSTEAD ( INSERT INTO nameless(a,b) VALUES (NEW.a, NEW.b) ; ); CREATE RULE clue_d AS ON DELETE TO clueless DO INSTEAD ( DELETE FROM nameless na WHERE na.a = OLD.a AND na.b = OLD.b ; ); CREATE RULE clue_u AS ON UPDATE TO clueless DO INSTEAD ( UPDATE nameless na SET a = NEW.a, b = NEW.b WHERE na.a = OLD.a AND na.b = OLD.b ; ); SELECT * FROM clueless; INSERT INTO clueless(a,b) SELECT 5+src.a, src.b-10 FROM clueless src WHERE src.a >=4 ; -- EXPLAIN ANALYZE UPDATE clueless SET b=b+20 WHERE b < 10 ; SELECT * FROM clueless; </code></pre> <p>Results:</p> <pre><code>NOTICE: drop cascades to view clueless DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "nameless_pkey" for table "nameless" CREATE TABLE CREATE VIEW INSERT 0 10 CREATE RULE CREATE RULE CREATE RULE a | b | c ---+----+---- 1 | 11 | 11 1 | 12 | 12 2 | 11 | 22 2 | 12 | 24 3 | 11 | 33 3 | 12 | 36 4 | 11 | 44 4 | 12 | 48 5 | 11 | 55 5 | 12 | 60 (10 rows) INSERT 0 4 UPDATE 4 a | b | c ----+----+----- 1 | 11 | 11 1 | 12 | 12 2 | 11 | 22 2 | 12 | 24 3 | 11 | 33 3 | 12 | 36 4 | 11 | 44 4 | 12 | 48 5 | 11 | 55 5 | 12 | 60 9 | 21 | 189 9 | 22 | 198 10 | 21 | 210 10 | 22 | 220 (14 rows) </code></pre> <p>Extra note: rules are <em>not</em> considered the Preferred Way to accomplish this kind of thing.</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