From 4c93b04f48e20af594abd82bf4b8c4c3e372b14a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 29 Aug 1999 19:22:28 +0000 Subject: [PATCH] Backpatch rule deparsing bugfixes into 6.5.*. I believe this is a pretty critical bugfix, since without it, stored rules containing CASE expressions or array references cannot be dumped by pg_dump. Worse, rules containing expressions like (a + b) * c will be dumped incorrectly (minus the parentheses, silently yielding the wrong answer...). --- src/backend/utils/adt/ruleutils.c | 408 ++++++++++++++-------------- src/test/regress/expected/rules.out | 102 +++---- 2 files changed, 249 insertions(+), 261 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 37b1e30b758..ee15bdd9f08 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -3,7 +3,7 @@ * out of it's tuple * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v 1.18.2.1 1999/08/02 05:24:57 scrappy Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v 1.18.2.2 1999/08/29 19:22:24 tgl Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -57,6 +57,11 @@ typedef struct QryHier Query *query; } QryHier; +typedef struct { + Index rt_index; + int levelsup; +} check_if_rte_used_context; + /* ---------- * Global data @@ -94,6 +99,7 @@ static char *get_select_query_def(Query *query, QryHier *qh); static char *get_insert_query_def(Query *query, QryHier *qh); static char *get_update_query_def(Query *query, QryHier *qh); static char *get_delete_query_def(Query *query, QryHier *qh); +static RangeTblEntry *get_rte_for_var(Var *var, QryHier *qh); static char *get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix); static char *get_func_expr(QryHier *qh, int rt_index, Expr *expr, bool varprefix); static char *get_tle_expr(QryHier *qh, int rt_index, TargetEntry *tle, bool varprefix); @@ -101,7 +107,9 @@ static char *get_const_expr(Const *constval); static char *get_sublink_expr(QryHier *qh, int rt_index, Node *node, bool varprefix); static char *get_relation_name(Oid relid); static char *get_attribute_name(Oid relid, int2 attnum); -static bool check_if_rte_used(int rt_index, Node *node, int sup); +static bool check_if_rte_used(Node *node, Index rt_index, int levelsup); +static bool check_if_rte_used_walker(Node *node, + check_if_rte_used_context *context); /* ---------- @@ -852,7 +860,7 @@ get_select_query_def(Query *query, QryHier *qh) List *l; /* ---------- - * First we need need to know which and how many of the + * First we need to know which and how many of the * range table entries in the query are used in the target list * or queries qualification * ---------- @@ -861,21 +869,15 @@ get_select_query_def(Query *query, QryHier *qh) rt_used = palloc(sizeof(bool) * rt_length); for (i = 0; i < rt_length; i++) { - if (check_if_rte_used(i + 1, (Node *) (query->targetList), 0)) + if (check_if_rte_used((Node *) (query->targetList), i + 1, 0) || + check_if_rte_used(query->qual, i + 1, 0) || + check_if_rte_used(query->havingQual, i + 1, 0)) { rt_used[i] = TRUE; rt_numused++; } else - { - if (check_if_rte_used(i + 1, (Node *) (query->qual), 0)) - { - rt_used[i] = TRUE; - rt_numused++; - } - else - rt_used[i] = FALSE; - } + rt_used[i] = FALSE; } /* ---------- @@ -919,14 +921,14 @@ get_select_query_def(Query *query, QryHier *qh) strcat(buf, get_tle_expr(qh, 0, tle, (rt_numused > 1))); /* Check if we must say AS ... */ - if (nodeTag(tle->expr) != T_Var) + if (! IsA(tle->expr, Var)) tell_as = strcmp(tle->resdom->resname, "?column?"); else { Var *var = (Var *) (tle->expr); char *attname; - rte = (RangeTblEntry *) nth(var->varno - 1, query->rtable); + rte = get_rte_for_var(var, qh); attname = get_attribute_name(rte->relid, var->varattno); if (strcmp(attname, tle->resdom->resname)) tell_as = TRUE; @@ -989,9 +991,14 @@ get_select_query_def(Query *query, QryHier *qh) sep = ""; foreach(l, query->groupClause) { + GroupClause *grp = (GroupClause *) lfirst(l); + Node *groupexpr; + + groupexpr = (Node *) get_groupclause_expr(grp, + query->targetList); strcat(buf, sep); + strcat(buf, get_rule_expr(qh, 0, groupexpr, (rt_numused > 1))); sep = ", "; - strcat(buf, get_rule_expr(qh, 0, lfirst(l), (rt_numused > 1))); } } @@ -1031,21 +1038,15 @@ get_insert_query_def(Query *query, QryHier *qh) rt_used = palloc(sizeof(bool) * rt_length); for (i = 0; i < rt_length; i++) { - if (check_if_rte_used(i + 1, (Node *) (query->targetList), 0)) + if (check_if_rte_used((Node *) (query->targetList), i + 1, 0) || + check_if_rte_used(query->qual, i + 1, 0) || + check_if_rte_used(query->havingQual, i + 1, 0)) { rt_used[i] = TRUE; rt_numused++; } else - { - if (check_if_rte_used(i + 1, (Node *) (query->qual), 0)) - { - rt_used[i] = TRUE; - rt_numused++; - } - else - rt_used[i] = FALSE; - } + rt_used[i] = FALSE; } i = 0; @@ -1199,6 +1200,20 @@ get_delete_query_def(Query *query, QryHier *qh) return pstrdup(buf); } +/* + * Find the RTE referenced by a (possibly nonlocal) Var. + */ +static RangeTblEntry * +get_rte_for_var(Var *var, QryHier *qh) +{ + int sup = var->varlevelsup; + + while (sup-- > 0) + qh = qh->parent; + + return (RangeTblEntry *) nth(var->varno - 1, qh->query->rtable); +} + /* ---------- * get_rule_expr - Parse back an expression @@ -1215,56 +1230,39 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) buf[0] = '\0'; /* ---------- - * Up to now I don't know if all the node types below - * can really occur in rules actions and qualifications. - * There might be some work left. + * Each level of get_rule_expr must return an indivisible term + * (parenthesized if necessary) to ensure result is reparsed into + * the same expression tree. + * + * There might be some work left here to support additional node types... * ---------- */ switch (nodeTag(node)) { - case T_TargetEntry: - { - TargetEntry *tle = (TargetEntry *) node; - - return get_rule_expr(qh, rt_index, - (Node *) (tle->expr), varprefix); - } - break; - - case T_Aggref: - { - Aggref *aggref = (Aggref *) node; - - strcat(buf, "\""); - strcat(buf, aggref->aggname); - strcat(buf, "\"("); - strcat(buf, get_rule_expr(qh, rt_index, - (Node *) (aggref->target), varprefix)); - strcat(buf, ")"); - return pstrdup(buf); - } + case T_Const: + return get_const_expr((Const *) node); break; - case T_GroupClause: + case T_Var: { - GroupClause *grp = (GroupClause *) node; - List *l; - TargetEntry *tle = NULL; + Var *var = (Var *) node; + RangeTblEntry *rte = get_rte_for_var(var, qh); - foreach(l, qh->query->targetList) + if (!strcmp(rte->refname, "*NEW*")) + strcat(buf, "new."); + else if (!strcmp(rte->refname, "*CURRENT*")) + strcat(buf, "old."); + else { - if (((TargetEntry *) lfirst(l))->resdom->resgroupref == - grp->tleGroupref) - { - tle = (TargetEntry *) lfirst(l); - break; - } + strcat(buf, "\""); + strcat(buf, rte->refname); + strcat(buf, "\"."); } + strcat(buf, "\""); + strcat(buf, get_attribute_name(rte->relid, var->varattno)); + strcat(buf, "\""); - if (tle == NULL) - elog(ERROR, "GROUP BY expression not found in targetlist"); - - return get_rule_expr(qh, rt_index, (Node *) tle, varprefix); + return pstrdup(buf); } break; @@ -1279,6 +1277,7 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) switch (expr->opType) { case OP_EXPR: + strcat(buf, "("); strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_leftop(expr), varprefix)); @@ -1288,6 +1287,7 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_rightop(expr), varprefix)); + strcat(buf, ")"); return pstrdup(buf); break; @@ -1296,7 +1296,7 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_leftop(expr), varprefix)); - strcat(buf, ") OR ("); + strcat(buf, " OR "); strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_rightop(expr), varprefix)); @@ -1309,7 +1309,7 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_leftop(expr), varprefix)); - strcat(buf, ") AND ("); + strcat(buf, " AND "); strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_rightop(expr), varprefix)); @@ -1318,7 +1318,7 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) break; case NOT_EXPR: - strcat(buf, "NOT ("); + strcat(buf, "(NOT "); strcat(buf, get_rule_expr(qh, rt_index, (Node *) get_leftop(expr), varprefix)); @@ -1334,50 +1334,77 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) default: printf("\n%s\n", nodeToString(node)); - elog(ERROR, "Expr not yet supported"); + elog(ERROR, "Expr type not supported"); } } break; - case T_Var: + case T_Aggref: { - Var *var = (Var *) node; - RangeTblEntry *rte; - int sup = var->varlevelsup; + Aggref *aggref = (Aggref *) node; - while (sup-- > 0) - qh = qh->parent; + strcat(buf, "\""); + strcat(buf, aggref->aggname); + strcat(buf, "\"("); + strcat(buf, get_rule_expr(qh, rt_index, + (Node *) (aggref->target), varprefix)); + strcat(buf, ")"); + return pstrdup(buf); + } + break; - rte = (RangeTblEntry *) nth(var->varno - 1, qh->query->rtable); + case T_ArrayRef: + { + ArrayRef *aref = (ArrayRef *) node; + List *lowlist; + List *uplist; - if (!strcmp(rte->refname, "*NEW*")) - strcat(buf, "new."); - else + strcat(buf, get_rule_expr(qh, rt_index, + aref->refexpr, varprefix)); + lowlist = aref->reflowerindexpr; + foreach(uplist, aref->refupperindexpr) { - if (!strcmp(rte->refname, "*CURRENT*")) - strcat(buf, "old."); - else + strcat(buf, "["); + if (lowlist) { - if (strcmp(rte->relname, rte->refname) != 0) - { - strcat(buf, "\""); - strcat(buf, rte->refname); - strcat(buf, "\"."); - } + strcat(buf, get_rule_expr(qh, rt_index, + (Node *) lfirst(lowlist), + varprefix)); + strcat(buf, ":"); + lowlist = lnext(lowlist); } + strcat(buf, get_rule_expr(qh, rt_index, + (Node *) lfirst(uplist), + varprefix)); + strcat(buf, "]"); } - strcat(buf, "\""); - strcat(buf, get_attribute_name(rte->relid, var->varattno)); - strcat(buf, "\""); - + /* XXX need to do anything with refassgnexpr? */ return pstrdup(buf); } break; - case T_List: + case T_CaseExpr: { - printf("\n%s\n", nodeToString(node)); - elog(ERROR, "List not yet supported"); + CaseExpr *caseexpr = (CaseExpr *) node; + List *temp; + + strcat(buf, "CASE"); + foreach(temp, caseexpr->args) + { + CaseWhen *when = (CaseWhen *) lfirst(temp); + + strcat(buf, " WHEN "); + strcat(buf, get_rule_expr(qh, rt_index, + when->expr, varprefix)); + strcat(buf, " THEN "); + strcat(buf, get_rule_expr(qh, rt_index, + when->result, varprefix)); + } + strcat(buf, " ELSE "); + strcat(buf, get_rule_expr(qh, rt_index, + caseexpr->defresult, varprefix)); + strcat(buf, " END"); + return pstrdup(buf); } break; @@ -1385,13 +1412,9 @@ get_rule_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) return get_sublink_expr(qh, rt_index, node, varprefix); break; - case T_Const: - return get_const_expr((Const *) node); - break; - default: printf("\n%s\n", nodeToString(node)); - elog(ERROR, "get_ruledef of %s: unknown node type %d get_rule_expr()", + elog(ERROR, "get_ruledef of %s: unknown node type %d in get_rule_expr()", rulename, nodeTag(node)); break; } @@ -1434,7 +1457,7 @@ get_func_expr(QryHier *qh, int rt_index, Expr *expr, bool varprefix) strcpy(buf, "("); strcat(buf, get_rule_expr(qh, rt_index, lfirst(expr->args), varprefix)); - strcat(buf, ") ISNULL"); + strcat(buf, " ISNULL)"); return pstrdup(buf); } if (!strcmp(proname, "nonnullvalue")) @@ -1442,7 +1465,7 @@ get_func_expr(QryHier *qh, int rt_index, Expr *expr, bool varprefix) strcpy(buf, "("); strcat(buf, get_rule_expr(qh, rt_index, lfirst(expr->args), varprefix)); - strcat(buf, ") NOTNULL"); + strcat(buf, " NOTNULL)"); return pstrdup(buf); } } @@ -1486,10 +1509,11 @@ get_func_expr(QryHier *qh, int rt_index, Expr *expr, bool varprefix) static char * get_tle_expr(QryHier *qh, int rt_index, TargetEntry *tle, bool varprefix) { - HeapTuple proctup; - Form_pg_proc procStruct; - Expr *expr; + Expr *expr = (Expr *) (tle->expr); Func *func; + HeapTuple tup; + Form_pg_proc procStruct; + Form_pg_type typeStruct; Const *second_arg; /* ---------- @@ -1497,12 +1521,9 @@ get_tle_expr(QryHier *qh, int rt_index, TargetEntry *tle, bool varprefix) * expression in the targetlist entry is a function call * ---------- */ - if (tle->resdom->restypmod < 0) - return get_rule_expr(qh, rt_index, tle->expr, varprefix); - if (nodeTag(tle->expr) != T_Expr) - return get_rule_expr(qh, rt_index, tle->expr, varprefix); - expr = (Expr *) (tle->expr); - if (expr->opType != FUNC_EXPR) + if (tle->resdom->restypmod < 0 || + ! IsA(expr, Expr) || + expr->opType != FUNC_EXPR) return get_rule_expr(qh, rt_index, tle->expr, varprefix); func = (Func *) (expr->oper); @@ -1511,12 +1532,11 @@ get_tle_expr(QryHier *qh, int rt_index, TargetEntry *tle, bool varprefix) * Get the functions pg_proc tuple * ---------- */ - proctup = SearchSysCacheTuple(PROOID, - ObjectIdGetDatum(func->funcid), 0, 0, 0); - if (!HeapTupleIsValid(proctup)) + tup = SearchSysCacheTuple(PROOID, + ObjectIdGetDatum(func->funcid), 0, 0, 0); + if (!HeapTupleIsValid(tup)) elog(ERROR, "cache lookup for proc %u failed", func->funcid); - - procStruct = (Form_pg_proc) GETSTRUCT(proctup); + procStruct = (Form_pg_proc) GETSTRUCT(tup); /* ---------- * It must be a function with two arguments where the first @@ -1524,22 +1544,34 @@ get_tle_expr(QryHier *qh, int rt_index, TargetEntry *tle, bool varprefix) * an int4. * ---------- */ - if (procStruct->pronargs != 2) - return get_rule_expr(qh, rt_index, tle->expr, varprefix); - if (procStruct->prorettype != procStruct->proargtypes[0]) - return get_rule_expr(qh, rt_index, tle->expr, varprefix); - if (procStruct->proargtypes[1] != INT4OID) + if (procStruct->pronargs != 2 || + procStruct->prorettype != procStruct->proargtypes[0] || + procStruct->proargtypes[1] != INT4OID) return get_rule_expr(qh, rt_index, tle->expr, varprefix); + /* + * Furthermore, the name of the function must be the same + * as the argument/result type name. + */ + tup = SearchSysCacheTuple(TYPOID, + ObjectIdGetDatum(procStruct->prorettype), + 0, 0, 0); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup for type %u failed", + procStruct->prorettype); + typeStruct = (Form_pg_type) GETSTRUCT(tup); + if (strncmp(procStruct->proname.data, typeStruct->typname.data, + NAMEDATALEN) != 0) + return get_rule_expr(qh, rt_index, tle->expr, varprefix); + /* ---------- * Finally (to be totally safe) the second argument must be a * const and match the value in the results atttypmod. * ---------- */ second_arg = (Const *) nth(1, expr->args); - if (nodeTag((Node *) second_arg) != T_Const) - return get_rule_expr(qh, rt_index, tle->expr, varprefix); - if ((int4) (second_arg->constvalue) != tle->resdom->restypmod) + if (! IsA(second_arg, Const) || + ((int4) second_arg->constvalue) != tle->resdom->restypmod) return get_rule_expr(qh, rt_index, tle->expr, varprefix); /* ---------- @@ -1567,12 +1599,12 @@ get_const_expr(Const *constval) char namebuf[64]; if (constval->constisnull) - return "NULL"; + return pstrdup("NULL"); typetup = SearchSysCacheTuple(TYPOID, ObjectIdGetDatum(constval->consttype), 0, 0, 0); if (!HeapTupleIsValid(typetup)) - elog(ERROR, "cache lookup of type %d failed", constval->consttype); + elog(ERROR, "cache lookup of type %u failed", constval->consttype); typeStruct = (Form_pg_type) GETSTRUCT(typetup); @@ -1604,6 +1636,8 @@ get_sublink_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) buf[0] = '\0'; + strcat(buf, "("); + if (sublink->lefthand != NULL) { if (length(sublink->lefthand) > 1) @@ -1656,7 +1690,7 @@ get_sublink_expr(QryHier *qh, int rt_index, Node *node, bool varprefix) strcat(buf, "("); strcat(buf, get_query_def(query, qh)); - strcat(buf, ")"); + strcat(buf, "))"); return pstrdup(buf); } @@ -1705,95 +1739,49 @@ get_attribute_name(Oid relid, int2 attnum) /* ---------- - * check_if_rte_used - Check a targetlist or qual - * if a given rangetable entry - * is used in it + * check_if_rte_used + * Check a targetlist or qual to see if a given rangetable entry + * is used in it * ---------- */ static bool -check_if_rte_used(int rt_index, Node *node, int sup) +check_if_rte_used(Node *node, Index rt_index, int levelsup) { - if (node == NULL) - return FALSE; - - switch (nodeTag(node)) - { - case T_TargetEntry: - { - TargetEntry *tle = (TargetEntry *) node; - - return check_if_rte_used(rt_index, - (Node *) (tle->expr), sup); - } - break; - - case T_Aggref: - { - Aggref *aggref = (Aggref *) node; - - return check_if_rte_used(rt_index, - (Node *) (aggref->target), sup); - } - break; - - case T_GroupClause: - return FALSE; - break; - - case T_Expr: - { - Expr *expr = (Expr *) node; - - return check_if_rte_used(rt_index, - (Node *) (expr->args), sup); - } - break; + check_if_rte_used_context context; - case T_Var: - { - Var *var = (Var *) node; - - return var->varno == rt_index && var->varlevelsup == sup; - } - break; - - case T_List: - { - List *l; - - foreach(l, (List *) node) - { - if (check_if_rte_used(rt_index, lfirst(l), sup)) - return TRUE; - } - return FALSE; - } - break; - - case T_SubLink: - { - SubLink *sublink = (SubLink *) node; - Query *query = (Query *) sublink->subselect; - - if (check_if_rte_used(rt_index, (Node *) (query->qual), sup + 1)) - return TRUE; - - if (check_if_rte_used(rt_index, (Node *) (sublink->lefthand), sup)) - return TRUE; - - return FALSE; - } - break; + context.rt_index = rt_index; + context.levelsup = levelsup; + return check_if_rte_used_walker(node, &context); +} - case T_Const: - return FALSE; - break; +static bool +check_if_rte_used_walker(Node *node, + check_if_rte_used_context *context) +{ + if (node == NULL) + return false; + if (IsA(node, Var)) + { + Var *var = (Var *) node; - default: - elog(ERROR, "get_ruledef of %s: unknown node type %d in check_if_rte_used()", - rulename, nodeTag(node)); - break; + return var->varno == context->rt_index && + var->varlevelsup == context->levelsup; } - - return FALSE; + if (IsA(node, SubLink)) + { + SubLink *sublink = (SubLink *) node; + Query *query = (Query *) sublink->subselect; + + /* Recurse into subquery; expression_tree_walker will not */ + if (check_if_rte_used((Node *) (query->targetList), + context->rt_index, context->levelsup + 1) || + check_if_rte_used(query->qual, + context->rt_index, context->levelsup + 1) || + check_if_rte_used(query->havingQual, + context->rt_index, context->levelsup + 1)) + return true; + /* fall through to let expression_tree_walker examine lefthand args */ + } + return expression_tree_walker(node, check_if_rte_used_walker, + (void *) context); } diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 1af61b37363..0bbe4aa3991 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1065,60 +1065,60 @@ sl8 | 21|brown | 40|inch | 101.6 (9 rows) QUERY: SELECT viewname, definition FROM pg_views 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_indexes |SELECT "c"."relname" AS "tablename", "i"."relname" AS "indexname", "pg_get_indexdef"("x"."indexrelid") AS "indexdef" FROM "pg_index" "x", "pg_class" "c", "pg_class" "i" WHERE ("c"."oid" = "x"."indrelid") AND ("i"."oid" = "x"."indexrelid"); -pg_rules |SELECT "c"."relname" AS "tablename", "r"."rulename", "pg_get_ruledef"("r"."rulename") AS "definition" FROM "pg_rewrite" "r", "pg_class" "c" WHERE ("r"."rulename" !~ '^_RET'::"text") AND ("c"."oid" = "r"."ev_class"); -pg_tables |SELECT "c"."relname" AS "tablename", "pg_get_userbyid"("c"."relowner") AS "tableowner", "c"."relhasindex" AS "hasindexes", "c"."relhasrules" AS "hasrules", "c"."reltriggers" > '0'::"int4" AS "hastriggers" FROM "pg_class" "c" WHERE (("c"."relkind" = 'r'::"char") OR ("c"."relkind" = 's'::"char")) AND (NOT (EXISTS (SELECT "rulename" FROM "pg_rewrite" WHERE ("ev_class" = "c"."oid") AND ("ev_type" = '1'::"char")))); -pg_user |SELECT "usename", "usesysid", "usecreatedb", "usetrace", "usesuper", "usecatupd", '********'::"text" AS "passwd", "valuntil" FROM "pg_shadow"; -pg_views |SELECT "c"."relname" AS "viewname", "pg_get_userbyid"("c"."relowner") AS "viewowner", "pg_get_viewdef"("c"."relname") AS "definition" FROM "pg_class" "c" WHERE ("c"."relhasrules") AND (EXISTS (SELECT "r"."rulename" FROM "pg_rewrite" "r" WHERE ("r"."ev_class" = "c"."oid") AND ("r"."ev_type" = '1'::"char"))); -rtest_v1 |SELECT "a", "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'::"int4" < (SELECT "count"("y"."a") AS "count" FROM "rtest_view2" "y" WHERE "y"."a" = "x"."a"); -rtest_vview2 |SELECT "a", "b" FROM "rtest_view1" WHERE "v"; -rtest_vview3 |SELECT "x"."a", "x"."b" FROM "rtest_vview2" "x" WHERE '0'::"int4" < (SELECT "count"("y"."a") 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 "a", "b", "rtest_viewfunc1"("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 "sl_name", "sl_avail", "sl_color", "sl_len", "sl_unit", "sl_len_cm" FROM "shoelace_obsolete" WHERE "sl_avail" = '0'::"int4"; -shoelace_obsolete |SELECT "sl_name", "sl_avail", "sl_color", "sl_len", "sl_unit", "sl_len_cm" FROM "shoelace" WHERE NOT (EXISTS (SELECT "shoename" FROM "shoe" WHERE "slcolor" = "sl_color")); -street |SELECT "r"."name", "r"."thepath", "c"."cname" FROM "road" "r", "real_city" "c" WHERE "c"."outline" ## "r"."thepath"; -toyemp |SELECT "name", "age", "location", '12'::"int4" * "salary" AS "annualsal" FROM "emp"; +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_indexes |SELECT "c"."relname" AS "tablename", "i"."relname" AS "indexname", "pg_get_indexdef"("x"."indexrelid") AS "indexdef" FROM "pg_index" "x", "pg_class" "c", "pg_class" "i" WHERE (("c"."oid" = "x"."indrelid") AND ("i"."oid" = "x"."indexrelid")); +pg_rules |SELECT "c"."relname" AS "tablename", "r"."rulename", "pg_get_ruledef"("r"."rulename") AS "definition" FROM "pg_rewrite" "r", "pg_class" "c" WHERE (("r"."rulename" !~ '^_RET'::"text") AND ("c"."oid" = "r"."ev_class")); +pg_tables |SELECT "c"."relname" AS "tablename", "pg_get_userbyid"("c"."relowner") AS "tableowner", "c"."relhasindex" AS "hasindexes", "c"."relhasrules" AS "hasrules", ("c"."reltriggers" > '0'::"int4") AS "hastriggers" FROM "pg_class" "c" WHERE ((("c"."relkind" = 'r'::"char") OR ("c"."relkind" = 's'::"char")) AND (NOT (EXISTS (SELECT "pg_rewrite"."rulename" FROM "pg_rewrite" WHERE (("pg_rewrite"."ev_class" = "c"."oid") AND ("pg_rewrite"."ev_type" = '1'::"char")))))); +pg_user |SELECT "pg_shadow"."usename", "pg_shadow"."usesysid", "pg_shadow"."usecreatedb", "pg_shadow"."usetrace", "pg_shadow"."usesuper", "pg_shadow"."usecatupd", '********'::"text" AS "passwd", "pg_shadow"."valuntil" FROM "pg_shadow"; +pg_views |SELECT "c"."relname" AS "viewname", "pg_get_userbyid"("c"."relowner") AS "viewowner", "pg_get_viewdef"("c"."relname") AS "definition" FROM "pg_class" "c" WHERE ("c"."relhasrules" AND (EXISTS (SELECT "r"."rulename" FROM "pg_rewrite" "r" WHERE (("r"."ev_class" = "c"."oid") AND ("r"."ev_type" = '1'::"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'::"int4" < (SELECT "count"("y"."a") 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'::"int4" < (SELECT "count"("y"."a") 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'::"int4"); +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 "road" "r", "real_city" "c" WHERE ("c"."outline" ## "r"."thepath"); +toyemp |SELECT "emp"."name", "emp"."age", "emp"."location", ('12'::"int4" * "emp"."salary") AS "annualsal" FROM "emp"; (20 rows) QUERY: SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; -tablename |rulename |definition --------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -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", "getpgusername"(), '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", "getpgusername"(), '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", "getpgusername"(), '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'::"int4") AND (new."a" < '20'::"int4") DO INSTEAD SELECT '1'::"int4"; -rtest_nothn1 |rtest_nothn_r2 |CREATE RULE "rtest_nothn_r2" AS ON INSERT TO "rtest_nothn1" WHERE (new."a" >= '30'::"int4") AND (new."a" < '40'::"int4") DO INSTEAD NOTHING; -rtest_nothn2 |rtest_nothn_r3 |CREATE RULE "rtest_nothn_r3" AS ON INSERT TO "rtest_nothn2" WHERE new."a" >= '100'::"int4" 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'::"text"), 'rule 1 - this should run 3rd or 4th'::"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'::"text"), 'rule 2 - this should run 1st'::"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'::"text"), 'rule 3 - this should run 3rd or 4th'::"text"); -rtest_order1 |rtest_order_r4 |CREATE RULE "rtest_order_r4" AS ON INSERT TO "rtest_order1" WHERE "a" < '100'::"int4" DO INSTEAD INSERT INTO "rtest_order2" ("a", "b", "c") VALUES (new."a", "nextval"('rtest_seq'::"text"), 'rule 4 - this should run 2nd'::"text"); -rtest_person |rtest_pers_del |CREATE RULE "rtest_pers_del" AS ON DELETE TO "rtest_person" DO DELETE FROM "rtest_admin" WHERE "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 "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 "sysname" = old."sysname"; DELETE FROM "rtest_admin" WHERE "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 "sysname" = old."sysname"; UPDATE rtest_admin SET "sysname" = new."sysname" WHERE "sysname" = old."sysname"; ); -rtest_t4 |rtest_t4_ins1 |CREATE RULE "rtest_t4_ins1" AS ON INSERT TO "rtest_t4" WHERE (new."a" >= '10'::"int4") AND (new."a" < '20'::"int4") 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'::"int4") AND (new."a" < '30'::"int4") 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'::"int4" 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'::"int4" 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 "a" = old."a"; -rtest_v1 |rtest_v1_ins |CREATE RULE "rtest_v1_ins" AS ON INSERT TO "rtest_v1" DO INSTEAD INSERT INTO "rtest_t1" ("a", "b") VALUES (new."a", new."b"); -rtest_v1 |rtest_v1_upd |CREATE RULE "rtest_v1_upd" AS ON UPDATE TO "rtest_v1" DO INSTEAD UPDATE rtest_t1 SET "a" = new."a", "b" = new."b" WHERE "a" = old."a"; -shoelace |shoelace_del |CREATE RULE "shoelace_del" AS ON DELETE TO "shoelace" DO INSTEAD DELETE FROM "shoelace_data" WHERE "sl_name" = old."sl_name"; -shoelace |shoelace_ins |CREATE RULE "shoelace_ins" AS ON INSERT TO "shoelace" DO INSTEAD INSERT INTO "shoelace_data" ("sl_name", "sl_avail", "sl_color", "sl_len", "sl_unit") VALUES (new."sl_name", new."sl_avail", new."sl_color", new."sl_len", new."sl_unit"); -shoelace |shoelace_upd |CREATE RULE "shoelace_upd" AS ON UPDATE TO "shoelace" DO INSTEAD UPDATE shoelace_data SET "sl_name" = new."sl_name", "sl_avail" = new."sl_avail", "sl_color" = new."sl_color", "sl_len" = new."sl_len", "sl_unit" = new."sl_unit" WHERE "sl_name" = old."sl_name"; -shoelace_data|log_shoelace |CREATE RULE "log_shoelace" AS ON UPDATE TO "shoelace_data" WHERE new."sl_avail" <> old."sl_avail" DO INSERT INTO "shoelace_log" ("sl_name", "sl_avail", "log_who", "log_when") VALUES (new."sl_name", new."sl_avail", 'Al Bundy'::"name", "datetime"('epoch'::"text")); -shoelace_ok |shoelace_ok_ins|CREATE RULE "shoelace_ok_ins" AS ON INSERT TO "shoelace_ok" DO INSTEAD UPDATE shoelace SET "sl_avail" = "sl_avail" + new."ok_quant" WHERE "sl_name" = new."ok_name"; +tablename |rulename |definition +-------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ +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", "getpgusername"(), '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", "getpgusername"(), '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", "getpgusername"(), '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'::"int4") AND (new."a" < '20'::"int4")) DO INSTEAD SELECT '1'::"int4"; +rtest_nothn1 |rtest_nothn_r2 |CREATE RULE "rtest_nothn_r2" AS ON INSERT TO "rtest_nothn1" WHERE ((new."a" >= '30'::"int4") AND (new."a" < '40'::"int4")) DO INSTEAD NOTHING; +rtest_nothn2 |rtest_nothn_r3 |CREATE RULE "rtest_nothn_r3" AS ON INSERT TO "rtest_nothn2" WHERE (new."a" >= '100'::"int4") 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'::"text"), 'rule 1 - this should run 3rd or 4th'::"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'::"text"), 'rule 2 - this should run 1st'::"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'::"text"), 'rule 3 - this should run 3rd or 4th'::"text"); +rtest_order1 |rtest_order_r4 |CREATE RULE "rtest_order_r4" AS ON INSERT TO "rtest_order1" WHERE ("rtest_order2"."a" < '100'::"int4") DO INSTEAD INSERT INTO "rtest_order2" ("a", "b", "c") VALUES (new."a", "nextval"('rtest_seq'::"text"), 'rule 4 - this should run 2nd'::"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'::"int4") AND (new."a" < '20'::"int4")) 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'::"int4") AND (new."a" < '30'::"int4")) 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'::"int4") 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'::"int4") 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 "rtest_t1" ("a", "b") VALUES (new."a", new."b"); +rtest_v1 |rtest_v1_upd |CREATE RULE "rtest_v1_upd" AS ON UPDATE TO "rtest_v1" DO INSTEAD UPDATE rtest_t1 SET "a" = new."a", "b" = new."b" WHERE ("rtest_t1"."a" = old."a"); +shoelace |shoelace_del |CREATE RULE "shoelace_del" AS ON DELETE TO "shoelace" DO INSTEAD DELETE FROM "shoelace_data" WHERE ("shoelace_data"."sl_name" = old."sl_name"); +shoelace |shoelace_ins |CREATE RULE "shoelace_ins" AS ON INSERT TO "shoelace" DO INSTEAD INSERT INTO "shoelace_data" ("sl_name", "sl_avail", "sl_color", "sl_len", "sl_unit") VALUES (new."sl_name", new."sl_avail", new."sl_color", new."sl_len", new."sl_unit"); +shoelace |shoelace_upd |CREATE RULE "shoelace_upd" AS ON UPDATE TO "shoelace" DO INSTEAD UPDATE shoelace_data SET "sl_name" = new."sl_name", "sl_avail" = new."sl_avail", "sl_color" = new."sl_color", "sl_len" = new."sl_len", "sl_unit" = new."sl_unit" WHERE ("shoelace_data"."sl_name" = old."sl_name"); +shoelace_data|log_shoelace |CREATE RULE "log_shoelace" AS ON UPDATE TO "shoelace_data" WHERE (new."sl_avail" <> old."sl_avail") DO INSERT INTO "shoelace_log" ("sl_name", "sl_avail", "log_who", "log_when") VALUES (new."sl_name", new."sl_avail", 'Al Bundy'::"name", "datetime"('epoch'::"text")); +shoelace_ok |shoelace_ok_ins|CREATE RULE "shoelace_ok_ins" AS ON INSERT TO "shoelace_ok" DO INSTEAD UPDATE shoelace SET "sl_avail" = ("shoelace"."sl_avail" + new."ok_quant") WHERE ("shoelace"."sl_name" = new."ok_name"); (27 rows) -- 2.39.5