From 59efda3e50ca4de6a9d5aa4491464e22b6329b1e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 10 Jul 2014 15:01:31 -0400 Subject: Implement IMPORT FOREIGN SCHEMA. This command provides an automated way to create foreign table definitions that match remote tables, thereby reducing tedium and chances for error. In this patch, we provide the necessary core-server infrastructure and implement the feature fully in the postgres_fdw foreign-data wrapper. Other wrappers will throw a "feature not supported" error until/unless they are updated. Ronan Dunklau and Michael Paquier, additional work by me --- contrib/postgres_fdw/sql/postgres_fdw.sql | 57 +++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 6187839453c..9f54359be58 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -609,3 +609,60 @@ UPDATE rem1 SET f2 = 'testo'; -- Test returning a system attribute INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; + +-- =================================================================== +-- test IMPORT FOREIGN SCHEMA +-- =================================================================== + +CREATE SCHEMA import_source; +CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); +CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX"); +CREATE TYPE typ1 AS (m1 int, m2 varchar); +CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); +CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); +CREATE TABLE import_source."x 5" (c1 float8); +ALTER TABLE import_source."x 5" DROP COLUMN c1; + +CREATE SCHEMA import_dest1; +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; +\det+ import_dest1 +\d import_dest1.* + +-- Options +CREATE SCHEMA import_dest2; +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 + OPTIONS (import_default 'true'); +\det+ import_dest2 +\d import_dest2.* +CREATE SCHEMA import_dest3; +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 + OPTIONS (import_collate 'false', import_not_null 'false'); +\det+ import_dest3 +\d import_dest3.* + +-- Check LIMIT TO and EXCEPT +CREATE SCHEMA import_dest4; +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch) + FROM SERVER loopback INTO import_dest4; +\det+ import_dest4 +IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch) + FROM SERVER loopback INTO import_dest4; +\det+ import_dest4 + +-- Assorted error cases +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; +IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4; +IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere; +IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere; + +-- Check case of a type present only on the remote server. +-- We can fake this by dropping the type locally in our transaction. +CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue'); +CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors"); + +CREATE SCHEMA import_dest5; +BEGIN; +DROP TYPE "Colors" CASCADE; +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5) + FROM SERVER loopback INTO import_dest5; -- ERROR +ROLLBACK; -- cgit v1.2.3