Hello.
Currently, T_CoalesceExpr is not executable on the foreign server.
This functionality could be useful and the fix appears to be
straightforward.
The proposed modification extends foreign_expr_walker to handle
T_CoalesceExpr node, deparseCoalesceExpr is added to
deparseExpr. A test case for COALESCE is also included.
Regards,
Pavel
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index ebe2c3a596a..c5f828a6575 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -190,6 +190,7 @@ static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root,
Index ignore_rel, List **ignore_conds,
List **additional_conds, List **params_list);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
+static void deparseCoalesceExpr(CoalesceExpr *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
static void appendOrderBySuffix(Oid sortop, Oid sortcoltype, bool nulls_first,
deparse_expr_cxt *context);
@@ -1034,6 +1035,33 @@ foreign_expr_walker(Node *node,
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_CoalesceExpr:
+ {
+ CoalesceExpr *cs = (CoalesceExpr *) node;
+
+ if (!foreign_expr_walker((Node *) cs->args,
+ glob_cxt, &inner_cxt, case_arg_cxt))
+ return false;
+
+ collation = cs->coalescecollid;
+
+ /*
+ * Detect whether node is introducing a collation not derived
+ * from a foreign Var. (If so, we just mark it unsafe for now
+ * rather than immediately returning false, since the parent
+ * node might not care.)
+ */
+ if (collation == InvalidOid)
+ state = FDW_COLLATE_NONE;
+ else if (inner_cxt.state == FDW_COLLATE_SAFE &&
+ collation == inner_cxt.collation)
+ state = FDW_COLLATE_SAFE;
+ else if (collation == DEFAULT_COLLATION_OID)
+ state = FDW_COLLATE_NONE;
+ else
+ state = FDW_COLLATE_UNSAFE;
+ }
+ break;
default:
/*
@@ -2961,6 +2989,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
case T_ArrayExpr:
deparseArrayExpr((ArrayExpr *) node, context);
break;
+ case T_CoalesceExpr:
+ deparseCoalesceExpr((CoalesceExpr *) node, context);
+ break;
case T_Aggref:
deparseAggref((Aggref *) node, context);
break;
@@ -3794,6 +3825,29 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
appendStringInfoChar(buf, ')');
}
+/*
+ * Deparse COALESCE node.
+ */
+static void
+deparseCoalesceExpr(CoalesceExpr *node, deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ bool first;
+ ListCell *arg;
+
+ appendStringInfoString(buf, "COALESCE(");
+
+ first = true;
+ foreach(arg, node->args)
+ {
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ deparseExpr((Expr *) lfirst(arg), context);
+ first = false;
+ }
+ appendStringInfoString(buf, ")");
+}
+
/*
* Append ORDER BY within aggregate function.
*/
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 6066510c7c0..a88b5999375 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -739,6 +739,14 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE coalesce(c3, 'NULL') <> 'NULL' ORDER BY c1; -- T_CoalesceExpr
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((COALESCE(c3, 'NULL'::text) <> 'NULL'::text)) ORDER BY "C 1" ASC NULLS LAST
+(3 rows)
+
-- parameterized remote path for foreign table
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 4f7ab2ed0ac..f7bfadbba82 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -345,6 +345,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar'; -- check special chars
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c8 = 'foo'; -- can't be sent to remote
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE coalesce(c3, 'NULL') <> 'NULL' ORDER BY c1; -- T_CoalesceExpr
-- parameterized remote path for foreign table
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
