📄 rules.out
字号:
------------+----------+------------+--------+----------+----------- sl1 | 5 | black | 80 | cm | 80 sl2 | 6 | black | 100 | cm | 100 sl3 | 0 | black | 35 | inch | 88.9 sl4 | 8 | black | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 0 | brown | 0.9 | m | 90 sl7 | 6 | brown | 60 | cm | 60 sl8 | 1 | brown | 40 | inch | 101.6(8 rows)insert into shoelace_ok select * from shoelace_arrive;SELECT * FROM shoelace ORDER BY sl_name; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- sl1 | 5 | black | 80 | cm | 80 sl2 | 6 | black | 100 | cm | 100 sl3 | 10 | black | 35 | inch | 88.9 sl4 | 8 | black | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 sl7 | 6 | brown | 60 | cm | 60 sl8 | 21 | brown | 40 | inch | 101.6(8 rows)SELECT * FROM shoelace_log ORDER BY sl_name; sl_name | sl_avail | log_who | log_when ------------+----------+----------+-------------------------- sl3 | 10 | Al Bundy | Thu Jan 01 00:00:00 1970 sl6 | 20 | Al Bundy | Thu Jan 01 00:00:00 1970 sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970 sl8 | 21 | Al Bundy | Thu Jan 01 00:00:00 1970(4 rows) CREATE VIEW shoelace_obsolete AS SELECT * FROM shoelace WHERE NOT EXISTS (SELECT shoename FROM shoe WHERE slcolor = sl_color); CREATE VIEW shoelace_candelete AS SELECT * FROM shoelace_obsolete WHERE sl_avail = 0;insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0);insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0);SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- sl9 | 0 | pink | 35 | inch | 88.9 sl10 | 1000 | magenta | 40 | inch | 101.6(2 rows)SELECT * FROM shoelace_candelete; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- sl9 | 0 | pink | 35 | inch | 88.9(1 row)DELETE FROM shoelace WHERE EXISTS (SELECT * FROM shoelace_candelete WHERE sl_name = shoelace.sl_name);SELECT * FROM shoelace ORDER BY sl_name; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- sl1 | 5 | black | 80 | cm | 80 sl10 | 1000 | magenta | 40 | inch | 101.6 sl2 | 6 | black | 100 | cm | 100 sl3 | 10 | black | 35 | inch | 88.9 sl4 | 8 | black | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 20 | brown | 0.9 | m | 90 sl7 | 6 | brown | 60 | cm | 60 sl8 | 21 | brown | 40 | inch | 101.6(9 rows)SELECT * FROM shoe ORDER BY shoename; shoename | sh_avail | slcolor | slminlen | slminlen_cm | slmaxlen | slmaxlen_cm | slunit ------------+----------+------------+----------+-------------+----------+-------------+---------- sh1 | 2 | black | 70 | 70 | 90 | 90 | cm sh2 | 0 | black | 30 | 76.2 | 40 | 101.6 | inch sh3 | 4 | brown | 50 | 50 | 65 | 65 | cm sh4 | 3 | brown | 40 | 101.6 | 50 | 127 | inch (4 rows)SELECT count(*) FROM shoe; count ------- 4(1 row)---- Simple test of qualified ON INSERT ... this did not work in 7.0 ...--create table foo (f1 int);create table foo2 (f1 int);create rule foorule as on insert to foo where f1 < 100do instead nothing;insert into foo values(1);insert into foo values(1001);select * from foo; f1 ------ 1001(1 row)drop rule foorule on foo;-- this should fail because f1 is not exposed for unqualified reference:create rule foorule as on insert to foo where f1 < 100do instead insert into foo2 values (f1);ERROR: column "f1" does not exist-- this is the correct way:create rule foorule as on insert to foo where f1 < 100do instead insert into foo2 values (new.f1);insert into foo values(2);insert into foo values(100);select * from foo; f1 ------ 1001 100(2 rows)select * from foo2; f1 ---- 2(1 row)drop rule foorule on foo;drop table foo;drop table foo2;---- Test rules containing INSERT ... SELECT, which is a very ugly special-- case as of 7.1. Example is based on bug report from Joel Burton.--create table pparent (pid int, txt text);insert into pparent values (1,'parent1');insert into pparent values (2,'parent2');create table cchild (pid int, descrip text);insert into cchild values (1,'descrip1');create view vview as select pparent.pid, txt, descrip from pparent left join cchild using (pid);create rule rrule as on update to vview do instead( insert into cchild (pid, descrip) select old.pid, new.descrip where old.descrip isnull; update cchild set descrip = new.descrip where cchild.pid = old.pid;);select * from vview; pid | txt | descrip -----+---------+---------- 1 | parent1 | descrip1 2 | parent2 | (2 rows)update vview set descrip='test1' where pid=1;select * from vview; pid | txt | descrip -----+---------+--------- 1 | parent1 | test1 2 | parent2 | (2 rows)update vview set descrip='test2' where pid=2;select * from vview; pid | txt | descrip -----+---------+--------- 1 | parent1 | test1 2 | parent2 | test2(2 rows)update vview set descrip='test3' where pid=3;select * from vview; pid | txt | descrip -----+---------+--------- 1 | parent1 | test1 2 | parent2 | test2(2 rows)select * from cchild; pid | descrip -----+--------- 1 | test1 2 | test2(2 rows)drop rule rrule on vview;drop view vview;drop table pparent;drop table cchild;---- Check that ruleutils are working--SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; viewname | definition --------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean); pg_prepared_xacts | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS "database" FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid, dbid oid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); pg_roles | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); pg_settings | SELECT a.name, a.setting, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text); pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin; pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 't'::"char")); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 't'::"char")) GROUP BY c.oid, n.nspname, c.relname; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit FROM pg_database d; pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (((pg_stat_all_indexes.schemaname = 'pg_catalog'::name) OR (pg_stat_all_indexes.schemaname = 'pg_toast'::name)) OR (pg_stat_all_indexes.schemaname = 'information_schema'::name)); pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del FROM pg_stat_all_tables WHERE (((pg_stat_all_tables.schemaname = 'pg_catalog'::name) OR (pg_stat_all_tables.schemaname = 'pg_toast'::name)) OR (pg_stat_all_tables.schemaname = 'information_schema'::name)); pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (((pg_stat_all_indexes.schemaname <> 'pg_catalog'::name) AND (pg_stat_all_indexes.schemaname <> 'pg_toast'::name)) AND (pg_stat_all_indexes.schemaname <> 'information_schema'::name)); pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del FROM pg_stat_all_tables WHERE (((pg_stat_all_tables.schemaname <> 'pg_catalog'::name) AND (pg_stat_all_tables.schemaname <> 'pg_toast'::name)) AND (pg_stat_all_tables.schemaname <> 'information_schema'::name)); pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 't'::"char")); pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char"); pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 't'::"char")) GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid; pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE (((pg_statio_all_indexes.schemaname = 'pg_catalog'::name) OR (pg_statio_all_indexes.schemaname = 'pg_toast'::name)) OR (pg_statio_all_indexes.schemaname = 'information_schema'::name)); pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE (((pg_statio_all_sequences.schemaname = 'pg_catalog'::name) OR (pg_statio_all_sequences.schemaname = 'pg_toast'::name)) OR (pg_statio_all_sequences.schemaname = 'information_schema'::name)); pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE (((pg_statio_all_tables.schemaname = 'pg_catalog'::name) OR (pg_statio_all_tables.schemaname = 'pg_toast'::name)) OR (pg_statio_all_tables.schemaname = 'information_schema'::name)); pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE (((pg_statio_all_indexes.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_indexes.schemaname <> 'pg_toast'::name)) AND (pg_statio_all_indexes.schemaname <> 'information_schema'::name)); pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE (((pg_statio_all_sequences.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_sequences.schemaname <> 'pg_toast'::name)) AND (pg_statio_all_sequences.schemaname <> 'information_schema'::name)); pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE (((pg_statio_all_tables.schemaname <> 'pg_catalog'::name) AND (pg_statio_all_tables.schemaname <> 'pg_toast'::name)) AND (pg_statio_all_tables.schemaname <> 'information_schema'::name)); pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE 1 WHEN s.stakind1 THEN s.stavalues1 WHEN s.stakind2 THEN s.stavalues2 WHEN s.stakind3 THEN s.stavalues3 WHEN s.stakind4 THEN s.stavalues4 ELSE NULL::"unknown" END AS most_common_vals, CASE 1 WHEN s.stakind1 THEN s.stanumbers1 WHEN s.stakind2 THEN s.stanumbers2 WHEN s.stakind3 THEN s.stanumbers3 WHEN s.stakind4 THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE 2 WHEN s.stakind1 THEN s.stavalues1 WHEN s.stakind2 THEN s.stavalues2 WHEN s.stakind3 THEN s.stavalues3 WHEN s.stakind4 THEN s.stavalues4 ELSE NULL::"unknown" END AS histogram_bounds, CASE 3 WHEN s.stakind1 THEN s.stanumbers1[1] WHEN s.stakind2 THEN s.stanumbers2[1] WHEN s.stakind3 THEN s.stanumbers3[1] WHEN s.stakind4 THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE has_table_privilege(c.oid, 'select'::text); pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, (c.reltriggers > 0) AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); pg_user | SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig FROM pg_shadow; pg_views | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char"); rtest_v1 | SELECT rtest_t1.a, rtest_t1.b FROM rtest_t1; rtest_vcomp | SELECT x.part, (x.size * y.factor) AS size_in_cm FROM rtest_comp x, rtest_unitfact y WHERE (x.unit = y.unit); rtest_vview1 | SELECT x.a, x.b FROM rtest_view1 x WHERE (0 < (SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a))); rtest_vview2 | SELECT rtest_view1.a, rtest_view1.b FROM rtest_view1 WHERE rtest_view1.v; rtest_vview3 | SELECT x.a, x.b FROM rtest_vview2 x WHERE (0 < (SELECT count(*) AS count FROM rtest_view2 y WHERE (y.a = x.a))); rtest_vview4 | SELECT x.a, x.b, count(y.a) AS refcount FROM rtest_view1 x, rtest_view2 y WHERE (x.a = y.a) GROUP BY x.a, x.b; rtest_vview5 | SELECT rtest_view1.a, rtest_view1.b, rtest_viewfunc1(rtest_view1.a) AS refcount FROM rtest_view1; shoe | SELECT sh.shoename, sh.sh_avail, sh.slcolor, sh.slminlen, (sh.slminlen * un.un_fact) AS slminlen_cm, sh.slmaxlen, (sh.slmaxlen * un.un_fact) AS slmaxlen_cm, sh.slunit FROM shoe_data sh, unit un WHERE (sh.slunit = un.un_name); shoe_ready | SELECT rsh.shoename, rsh.sh_avail, rsl.sl_name, rsl.sl_avail, int4smaller(rsh.sh_avail, rsl.sl_avail) AS total_avail FROM shoe rsh, shoelace rsl WHERE (((rsl.sl_color = rsh.slcolor) AND (rsl.sl_len_cm >= rsh.slminlen_cm)) AND (rsl.sl_len_cm <= rsh.slmaxlen_cm)); shoelace | SELECT s.sl_name, s.sl_avail, s.sl_color, s.sl_len, s.sl_unit, (s.sl_len * u.un_fact) AS sl_len_cm FROM shoelace_data s, unit u WHERE (s.sl_unit = u.un_name); shoelace_candelete | SELECT shoelace_obsolete.sl_name, shoelace_obsolete.sl_avail, shoelace_obsolete.sl_color, shoelace_obsolete.sl_len, shoelace_obsolete.sl_unit, shoelace_obsolete.sl_len_cm FROM shoelace_obsolete WHERE (shoelace_obsolete.sl_avail = 0); shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;(44 rows)SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; tablename | rulename | definition ---------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary); rtest_emp | rtest_emp_ins | CREATE RULE rtest_emp_ins AS ON INSERT TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'hired'::bpchar, new.salary, '$0.00'::money); rtest_emp | rtest_emp_upd | CREATE RULE rtest_emp_upd AS ON UPDATE TO rtest_emp WHERE (new.salary <> old.salary) DO INSERT INTO rtest_emplog (ename, who, "action", newsal, oldsal) VALUES (new.ename, "current_user"(), 'honored'::bpchar, new.salary, old.salary); rtest_nothn1 | rtest_nothn_r1 | CREATE RULE rtest_nothn_r1 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD NOTHING; rtest_nothn1 | rtest_nothn_r2 | CREATE RULE rtest_nothn_r2 AS ON INSERT TO rtest_nothn1 WHERE ((new.a >= 30) AND (new.a < 40)) DO INSTEAD NOTHING; rtest_nothn2 | rtest_nothn_r3 | CREATE RULE rtest_nothn_r3 AS ON INSERT TO rtest_nothn2 WHERE (new.a >= 100) DO INSTEAD INSERT INTO rtest_nothn3 (a, b) VALUES (new.a, new.b); rtest_nothn2 | rtest_nothn_r4 | CREATE RULE rtest_nothn_r4 AS ON INSERT TO rtest_nothn2 DO INSTEAD NOTHING; rtest_order1 | rtest_order_r1 | CREATE RULE rtest_order_r1 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 1 - this should run 1st'::text); rtest_order1 | rtest_order_r2 | CREATE RULE rtest_order_r2 AS ON INSERT TO rtest_order1 DO INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 2 - this should run 2nd'::text); rtest_order1 | rtest_order_r3 | CREATE RULE rtest_order_r3 AS ON INSERT TO rtest_order1 DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 3 - this should run 3rd'::text); rtest_order1 | rtest_order_r4 | CREATE RULE rtest_order_r4 AS ON INSERT TO rtest_order1 WHERE (new.a < 100) DO INSTEAD INSERT INTO rtest_order2 (a, b, c) VALUES (new.a, nextval('rtest_seq'::regclass), 'rule 4 - this should run 4th'::text); rtest_person | rtest_pers_del | CREATE RULE rtest_pers_del AS ON DELETE TO rtest_person DO DELETE FROM rtest_admin WHERE (rtest_admin.pname = old.pname); rtest_person | rtest_pers_upd | CREATE RULE rtest_pers_upd AS ON UPDATE TO rtest_person DO UPDATE rtest_admin SET pname = new.pname WHERE (rtest_admin.pname = old.pname); rtest_system | rtest_sys_del | CREATE RULE rtest_sys_del AS ON DELETE TO rtest_system DO (DELETE FROM rtest_interface WHERE (rtest_interface.sysname = old.sysname); DELETE FROM rtest_admin WHERE (rtest_admin.sysname = old.sysname); ); rtest_system | rtest_sys_upd | CREATE RULE rtest_sys_upd AS ON UPDATE TO rtest_system DO (UPDATE rtest_interface SET sysname = new.sysname WHERE (rtest_interface.sysname = old.sysname); UPDATE rtest_admin SET sysname = new.sysname WHERE (rtest_admin.sysname = old.sysname); ); rtest_t4 | rtest_t4_ins1 | CREATE RULE rtest_t4_ins1 AS ON INSERT TO rtest_t4 WHERE ((new.a >= 10) AND (new.a < 20)) DO INSTEAD INSERT INTO rtest_t5 (a, b) VALUES (new.a, new.b); rtest_t4 | rtest_t4_ins2 | CREATE RULE rtest_t4_ins2 AS ON INSERT TO rtest_t4 WHERE ((new.a >= 20) AND (new.a < 30)) DO INSERT INTO rtest_t6 (a, b) VALUES (new.a, new.b); rtest_t5 | rtest_t5_ins | CREATE RULE rtest_t5_ins AS ON INSERT TO rtest_t5 WHERE (new.a > 15) DO INSERT INTO rtest_t7 (a, b) VALUES (new.a, new.b); rtest_t6 | rtest_t6_ins | CREATE RULE rtest_t6_ins AS ON INSERT TO rtest_t6 WHERE (new.a > 25) DO INSTEAD INSERT INTO rtest_t8 (a, b) VALUES (new.a, new.b); rtest_v1 | rtest_v1_del | CREATE RULE rtest_v1_del AS ON DELETE TO rtest_v1 DO INSTEAD DELETE FROM rtest_t1 WHERE (rtest_t1.a = old.a); rtest_v1 | rtest_v1_ins | CREATE RULE rtest_v1_ins AS ON INSERT TO rtest_v1 DO INSTEAD INSERT INTO rt⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -