Full use of a custom data type with postgres_fdw currently requires the
type be maintained in both the local and remote databases. `CREATE
FOREIGN TABLE` does not check declared types against the remote table,
but declaring e.g. a remote enum to be local text works only partway, as
seen here. A simple select query against alpha_items returns the enum
values as text; however, *filtering* on the column yields an error.
create database alpha;
create database beta;
\c alpha
create type itemtype as enum ('one', 'two', 'three');
create table items (
id serial not null primary key,
type itemtype not null
);
insert into items (type) values ('one'), ('one'), ('two');
\c beta
create extension postgres_fdw;
create server alpha foreign data wrapper postgres_fdw options (dbname 'alpha',
host 'localhost', port '5432');
create user mapping for postgres server alpha options (user 'postgres');
create foreign table alpha_items (
id int,
type text
) server alpha options (table_name 'items');
select * from alpha_items; -- ok
select * from alpha_items where type = 'one';
ERROR: operator does not exist: public.itemtype = text
HINT: No operator matches the given name and argument types. You might need to
add explicit type casts.
CONTEXT: remote SQL command: SELECT id, type FROM public.items WHERE ((type =
'one'::text))
The attached changeset adds a new boolean option for postgres_fdw
foreign table columns, `use_local_type`. When true, ColumnRefs for the
relevant attribute will be deparsed with a cast to the type defined in
`CREATE FOREIGN TABLE`.
create foreign table alpha_items (
id int,
type text options (use_local_type 'true')
) server alpha options (table_name 'items');
select * from alpha_items where type = 'one'; -- succeeds
This builds and checks, with a new regression test and documentation.
From 09961e10daf72e2c1fbbdddb05b2940b2da14df0 Mon Sep 17 00:00:00 2001
From: Dian M Fay <[email protected]>
Date: Mon, 1 Mar 2021 00:44:15 -0500
Subject: [PATCH] postgres_fdw: column option to override foreign types
Enabling the use_local_type option on a foreign table column will
deparse it with a cast to the locally-declared type, allowing overrides
of foreign types not known to the local database.
---
contrib/postgres_fdw/deparse.c | 15 ++++++++++++---
contrib/postgres_fdw/expected/postgres_fdw.out | 17 +++++++++++++++++
contrib/postgres_fdw/option.c | 1 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 10 ++++++++++
doc/src/sgml/postgres-fdw.sgml | 13 +++++++++++++
5 files changed, 53 insertions(+), 3 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 6faf499f9a..c722d49316 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -2264,6 +2264,7 @@ deparseColumnRef(StringInfo buf, int varno, int varattno,
RangeTblEntry *rte,
else
{
char *colname = NULL;
+ bool uselocaltype = NULL;
List *options;
ListCell *lc;
@@ -2280,10 +2281,9 @@ deparseColumnRef(StringInfo buf, int varno, int
varattno, RangeTblEntry *rte,
DefElem *def = (DefElem *) lfirst(lc);
if (strcmp(def->defname, "column_name") == 0)
- {
colname = defGetString(def);
- break;
- }
+ else if (strcmp(def->defname, "use_local_type") == 0)
+ uselocaltype = defGetBoolean(def);
}
/*
@@ -2297,6 +2297,15 @@ deparseColumnRef(StringInfo buf, int varno, int
varattno, RangeTblEntry *rte,
ADD_REL_QUALIFIER(buf, varno);
appendStringInfoString(buf, quote_identifier(colname));
+
+ if (uselocaltype)
+ {
+ Oid coltype = get_atttype(rte->relid, varattno);
+ char *typname = deparse_type_name(coltype, -1);
+
+ appendStringInfoString(buf, "::");
+ appendStringInfoString(buf, typname);
+ }
}
}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0649b6b81c..c3271bac8f 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4105,6 +4105,23 @@ ERROR: invalid input syntax for type integer: "foo"
CONTEXT: processing expression at position 2 in select list
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
-- ===================================================================
+-- conversion with use_local_type
+-- ===================================================================
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE text;
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1; -- ERROR
+ERROR: operator does not exist: public.user_enum = text
+HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
+CONTEXT: remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S
1"."T 1" WHERE ((c8 = 'foo'::text)) LIMIT 1::bigint
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (use_local_type 'true');
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+ c1 | c2 | c3 | c4 | c5 |
c6 | c7 | c8
+----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1
| 1 | foo
+(1 row)
+
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET use_local_type 'false');
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
+-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
-- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 64698c4da3..4d9ed3fc0f 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -200,6 +200,7 @@ InitPgFdwOptions(void)
{"schema_name", ForeignTableRelationId, false},
{"table_name", ForeignTableRelationId, false},
{"column_name", AttributeRelationId, false},
+ {"use_local_type", AttributeRelationId, false},
/* use_remote_estimate is available on both server and table */
{"use_remote_estimate", ForeignServerRelationId, false},
{"use_remote_estimate", ForeignTableRelationId, false},
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 2b525ea44a..5db2e2b575 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1133,6 +1133,16 @@ SELECT ft1.c1, ft2.c2, ft1 FROM ft1, ft2 WHERE ft1.c1
= ft2.c1 AND ft1.c1 = 1;
SELECT sum(c2), array_agg(c8) FROM ft1 GROUP BY c8; -- ERROR
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
+-- ===================================================================
+-- conversion with use_local_type
+-- ===================================================================
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE text;
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1; -- ERROR
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (use_local_type 'true');
+SELECT * FROM ft1 WHERE c8 = 'foo' LIMIT 1;
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET use_local_type 'false');
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
+
-- ===================================================================
-- subtransaction
-- + local/remote error doesn't break cursor
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 07aa25799d..8486799e19 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -219,6 +219,19 @@ OPTIONS (ADD password_required 'false');
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>use_local_type</literal></term>
+ <listitem>
+ <para>
+ A foreign table column with this option set to <literal>true</literal>
+ will attempt to cast values and references to the type declared in
+ <command>CREATE FOREIGN TABLE</command>. This allows, for example,
+ a text column to represent an enum column in the remote table without
+ maintaining the enum type in the local database as well.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect3>
--
2.30.1
