--SET log_min_messages TO DEBUG1; --SET client_min_messages TO DEBUG1; --Testcase 001: CREATE EXTENSION sqlite_fdw; --Testcase 002: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlite_fdw_test/common.db'); --Testcase 003: CREATE SERVER sqlite2 FOREIGN DATA WRAPPER sqlite_fdw; --Testcase 009: CREATE FOREIGN TABLE "type_UUID"( "i" int OPTIONS (key 'true'), "u" uuid) SERVER sqlite_svr OPTIONS (table 'type_UUID'); --Testcase 010: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" TYPE text; --Testcase 011: INSERT INTO "type_UUID" ("i", "u") VALUES (1, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'); --Testcase 012: INSERT INTO "type_UUID" ("i", "u") VALUES (2, 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'); --Testcase 013: INSERT INTO "type_UUID" ("i", "u") VALUES (3, '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}'); --Testcase 014: INSERT INTO "type_UUID" ("i", "u") VALUES (4, 'a0eebc999c0b4ef8bb6d6bb9bd380a11'); --Testcase 015: INSERT INTO "type_UUID" ("i", "u") VALUES (5, 'a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11'); --Testcase 016: INSERT INTO "type_UUID" ("i", "u") VALUES (6, '{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}'); --Testcase 017: INSERT INTO "type_UUID" ("i", "u") VALUES (7, 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'); --Testcase 018: INSERT INTO "type_UUID" ("i", "u") VALUES (8, 'B0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'); --Testcase 019: INSERT INTO "type_UUID" ("i", "u") VALUES (9, '{b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12}'); --Testcase 020: INSERT INTO "type_UUID" ("i", "u") VALUES (10, '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); --Testcase 021: INSERT INTO "type_UUID" ("i", "u") VALUES (11, 'b0eebc999c0b4ef8bb6d6bb9bd380a12'); --Testcase 022: INSERT INTO "type_UUID" ("i", "u") VALUES (12, 'b0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a12'); --Testcase 023: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" TYPE bytea; --Testcase 024: INSERT INTO "type_UUID" ("i", "u") VALUES (13, decode('a0eebc999c0b4ef8bb6d6bb9bd380a11', 'hex')); --Testcase 025: INSERT INTO "type_UUID" ("i", "u") VALUES (14, decode('b0eebc999c0b4ef8bb6d6bb9bd380a12', 'hex')); --Testcase 026: INSERT INTO "type_UUID" ("i", "u") VALUES (15, decode('a0eebc999c0b4ef8bb6d6bb9bd380a11', 'hex')); --Testcase 027: INSERT INTO "type_UUID" ("i", "u") VALUES (16, decode('b0eebc999c0b4ef8bb6d6bb9bd380a12', 'hex')); --Testcase 028: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" TYPE uuid; --Testcase 029: INSERT INTO "type_UUID" ("i", "u") VALUES (17, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'); --Testcase 030: INSERT INTO "type_UUID" ("i", "u") VALUES (18, 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'); --Testcase 031: INSERT INTO "type_UUID" ("i", "u") VALUES (19, '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}'); --Testcase 032: INSERT INTO "type_UUID" ("i", "u") VALUES (20, 'a0eebc999c0b4ef8bb6d6bb9bd380a11'); --Testcase 033: INSERT INTO "type_UUID" ("i", "u") VALUES (21, 'a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11'); --Testcase 034: INSERT INTO "type_UUID" ("i", "u") VALUES (22, '{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}'); --Testcase 035: INSERT INTO "type_UUID" ("i", "u") VALUES (23, 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'); --Testcase 036: INSERT INTO "type_UUID" ("i", "u") VALUES (24, 'B0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'); --Testcase 037: INSERT INTO "type_UUID" ("i", "u") VALUES (25, '{b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12}'); --Testcase 038: INSERT INTO "type_UUID" ("i", "u") VALUES (26, 'b0eebc999c0b4ef8bb6d6bb9bd380a12'); --Testcase 039: INSERT INTO "type_UUID" ("i", "u") VALUES (27, 'b0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a12'); --Testcase 040: INSERT INTO "type_UUID" ("i", "u") VALUES (28, '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); --Testcase 041: EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO "type_UUID" ("i", "u") VALUES (28, '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); QUERY PLAN ------------------------------------------------------------------ Insert on public."type_UUID" Batch Size: 1 -> Result Output: 28, 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'::uuid (4 rows) --Testcase 042: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (ADD column_type 'BLOB'); --Testcase 043: INSERT INTO "type_UUID" ("i", "u") VALUES (29, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'); --Testcase 044: INSERT INTO "type_UUID" ("i", "u") VALUES (30, 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'); --Testcase 045: INSERT INTO "type_UUID" ("i", "u") VALUES (31, '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}'); --Testcase 046: INSERT INTO "type_UUID" ("i", "u") VALUES (32, 'a0eebc999c0b4ef8bb6d6bb9bd380a11'); --Testcase 047: INSERT INTO "type_UUID" ("i", "u") VALUES (33, 'a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11'); --Testcase 048: INSERT INTO "type_UUID" ("i", "u") VALUES (34, '{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}'); --Testcase 049: INSERT INTO "type_UUID" ("i", "u") VALUES (35, 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'); --Testcase 050: INSERT INTO "type_UUID" ("i", "u") VALUES (36, 'B0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'); --Testcase 051: INSERT INTO "type_UUID" ("i", "u") VALUES (37, '{b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12}'); --Testcase 052: INSERT INTO "type_UUID" ("i", "u") VALUES (38, 'b0eebc999c0b4ef8bb6d6bb9bd380a12'); --Testcase 053: INSERT INTO "type_UUID" ("i", "u") VALUES (39, 'b0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a12'); --Testcase 054: INSERT INTO "type_UUID" ("i", "u") VALUES (40, '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); --Testcase 055: EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO "type_UUID" ("i", "u") VALUES (39, 'b0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a12'); QUERY PLAN ------------------------------------------------------------------ Insert on public."type_UUID" Batch Size: 1 -> Result Output: 39, 'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12'::uuid (4 rows) --Testcase 056: CREATE FOREIGN TABLE "type_UUID+"( "i" int OPTIONS (key 'true'), "u" uuid, "t" text, "l" smallint) SERVER sqlite_svr OPTIONS (table 'type_UUID+'); --Testcase 057: SELECT * FROM "type_UUID+"; i | u | t | l ----+--------------------------------------+------+---- 1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 3 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 38 4 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 32 5 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 39 6 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 37 7 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 8 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 9 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 38 10 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 37 11 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 32 12 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 39 13 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 14 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 15 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 16 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 17 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 18 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 19 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 20 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 21 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 22 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 23 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 24 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 25 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 26 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 27 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 28 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 29 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 30 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 31 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 32 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 33 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 34 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 35 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 36 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 37 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 38 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 39 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 40 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 (40 rows) --Testcase 058: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'BLOB'); --Testcase 059: SELECT * FROM "type_UUID+" where "u" = 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'; i | u | t | l ----+--------------------------------------+------+---- 1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 3 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 38 4 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 32 5 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 39 6 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 37 13 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 15 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 17 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 18 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 19 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 20 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 21 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 22 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 29 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 30 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 31 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 32 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 33 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 34 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 (20 rows) --Testcase 060: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_UUID+" where "u" = 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public."type_UUID+" Output: i, u, t, l SQLite query: SELECT `i`, sqlite_fdw_uuid_blob(`u`), `t`, `l` FROM main."type_UUID+" WHERE ((sqlite_fdw_uuid_blob(`u`) = X'a0eebc999c0b4ef8bb6d6bb9bd380a11')) (3 rows) --Testcase 061: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'TEXT'); --Testcase 062: SELECT * FROM "type_UUID+" where "u" = 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'; i | u | t | l ----+--------------------------------------+------+---- 1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 3 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 38 4 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 32 5 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 39 6 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 37 13 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 15 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 17 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 18 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 19 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 20 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 21 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 22 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 29 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 30 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 31 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 32 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 33 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 34 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 (20 rows) --Testcase 063: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_UUID+" where "u" = 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public."type_UUID+" Output: i, u, t, l SQLite query: SELECT `i`, sqlite_fdw_uuid_blob(`u`), `t`, `l` FROM main."type_UUID+" WHERE ((sqlite_fdw_uuid_blob(`u`) = X'a0eebc999c0b4ef8bb6d6bb9bd380a11')) (3 rows) --Testcase 064: SELECT * FROM "type_UUID+" where "u" = 'B0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'; i | u | t | l ----+--------------------------------------+------+---- 7 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 8 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 9 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 38 10 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 37 11 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 32 12 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 39 14 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 16 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 23 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 24 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 25 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 26 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 27 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 28 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 35 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 36 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 37 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 38 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 39 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 40 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 (20 rows) --Testcase 065: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'BLOB'); --Testcase 066: SELECT * FROM "type_UUID+" where "u" = 'B0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A12'; i | u | t | l ----+--------------------------------------+------+---- 7 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 8 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 9 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 38 10 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 37 11 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 32 12 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 39 14 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 16 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 23 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 24 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 25 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 26 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 27 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 28 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | text | 36 35 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 36 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 37 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 38 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 39 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 40 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 | blob | 16 (20 rows) --Testcase 067: UPDATE "type_UUID" SET "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}' WHERE "i" = 25; --Testcase 068: EXPLAIN (VERBOSE, COSTS OFF) UPDATE "type_UUID" SET "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}' WHERE "i" = 25; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Update on public."type_UUID" -> Foreign Update on public."type_UUID" SQLite query: UPDATE main."type_UUID" SET `u` = X'b0eebc999c0b4ef8bb6d6bb9bd380a15' WHERE ((`i` = 25)) (3 rows) --Testcase 069: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'TEXT'); --Testcase 070: EXPLAIN (VERBOSE, COSTS OFF) UPDATE "type_UUID" SET "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}' WHERE "i" = 25; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Update on public."type_UUID" -> Foreign Update on public."type_UUID" SQLite query: UPDATE main."type_UUID" SET `u` = sqlite_fdw_uuid_str(X'b0eebc999c0b4ef8bb6d6bb9bd380a15') WHERE ((`i` = 25)) (3 rows) --Testcase 071: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'BLOB'); --Testcase 072: DELETE FROM "type_UUID" WHERE "u" = 'b0eebc999c0b4ef8bb6d6bb9bd380a12'; --Testcase 073: EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM "type_UUID" WHERE "u" = 'b0eebc999c0b4ef8bb6d6bb9bd380a12'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Delete on public."type_UUID" -> Foreign Delete on public."type_UUID" SQLite query: DELETE FROM main."type_UUID" WHERE ((sqlite_fdw_uuid_blob(`u`) = X'b0eebc999c0b4ef8bb6d6bb9bd380a12')) (3 rows) --Testcase 074: SELECT * FROM "type_UUID+"; i | u | t | l ----+--------------------------------------+------+---- 1 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 2 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 3 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 38 4 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 32 5 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 39 6 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 37 13 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 15 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 17 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 18 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 19 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 20 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 21 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 22 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | text | 36 25 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a15 | blob | 16 29 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 30 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 31 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 32 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 33 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 34 | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | blob | 16 (21 rows) --Testcase 075: DELETE FROM "type_UUID" WHERE "u" = 'a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11'; --Testcase 076: SELECT * FROM "type_UUID+"; i | u | t | l ----+--------------------------------------+------+---- 25 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a15 | blob | 16 (1 row) --Testcase 077: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'TEXT'); --Testcase 075: DELETE FROM "type_UUID" WHERE "u" = 'b0eebc999c0b4ef8bb6d6bb9bd380a15'; --Testcase 076: EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM "type_UUID" WHERE "u" = 'b0eebc999c0b4ef8bb6d6bb9bd380a15'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Delete on public."type_UUID" -> Foreign Delete on public."type_UUID" SQLite query: DELETE FROM main."type_UUID" WHERE ((sqlite_fdw_uuid_blob(`u`) = X'b0eebc999c0b4ef8bb6d6bb9bd380a15')) (3 rows) --Testcase 077: SELECT * FROM "type_UUID+"; i | u | t | l ---+---+---+--- (0 rows) --Testcase 078: INSERT INTO "type_UUID" ("i", "u") VALUES (41, '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}'); --Testcase 079: SELECT * FROM "type_UUID+" WHERE "i" = 41; i | u | t | l ----+--------------------------------------+------+---- 41 | b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a15 | text | 36 (1 row) --Testcase 080: UPDATE "type_UUID" SET "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-00000a15}' WHERE "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}'; --Testcase 081: EXPLAIN (VERBOSE, COSTS OFF) UPDATE "type_UUID" SET "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-00000a15}' WHERE "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on public."type_UUID" -> Foreign Update on public."type_UUID" SQLite query: UPDATE main."type_UUID" SET `u` = sqlite_fdw_uuid_str(X'b0eebc999c0b4ef8bb6d6bb900000a15') WHERE ((sqlite_fdw_uuid_blob(`u`) = X'b0eebc999c0b4ef8bb6d6bb9bd380a15')) (3 rows) --Testcase 082: SELECT * FROM "type_UUID+"; i | u | t | l ----+--------------------------------------+------+---- 41 | b0eebc99-9c0b-4ef8-bb6d-6bb900000a15 | text | 36 (1 row) --Testcase 083: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" OPTIONS (SET column_type 'BLOB'); --Testcase 084: EXPLAIN (VERBOSE, COSTS OFF) UPDATE "type_UUID" SET "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a15}' WHERE "u" = '{b0eebc99-9c0b4ef8-bb6d6bb9-00000a15}'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on public."type_UUID" -> Foreign Update on public."type_UUID" SQLite query: UPDATE main."type_UUID" SET `u` = X'b0eebc999c0b4ef8bb6d6bb9bd380a15' WHERE ((sqlite_fdw_uuid_blob(`u`) = X'b0eebc999c0b4ef8bb6d6bb900000a15')) (3 rows) --Testcase 085: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" TYPE bytea; --Testcase 086: INSERT INTO "type_UUID" ("i", "u") VALUES (42, decode('a0eebc999c0b4ef8bb6d6bb9bd380a11f1', 'hex')); --Testcase 087: INSERT INTO "type_UUID" ("i", "u") VALUES (43, decode('b0eebc999c0b4ef8bb6d6bb9bd380a', 'hex')); --Testcase 088: ALTER FOREIGN TABLE "type_UUID" ALTER COLUMN "u" TYPE uuid; --Testcase 089: SELECT * FROM "type_UUID+" WHERE "i" = 42; ERROR: PostgreSQL uuid data type allows only 16 bytes SQLite blob value HINT: SQLite value with "blob" affinity (17 bytes) in hex : a0eebc999c0b4ef8bb6d6bb9bd380a11f1 CONTEXT: foreign table "type_UUID+" foreign column "u" have data type "uuid" (usual affinity "blob"), in query there is reference to foreign column --Testcase 090: SELECT * FROM "type_UUID+" WHERE "i" = 43; ERROR: PostgreSQL uuid data type allows only 16 bytes SQLite blob value HINT: SQLite value with "blob" affinity (15 bytes) in hex : b0eebc999c0b4ef8bb6d6bb9bd380a CONTEXT: foreign table "type_UUID+" foreign column "u" have data type "uuid" (usual affinity "blob"), in query there is reference to foreign column --Testcase 091: EXPLAIN (VERBOSE, COSTS OFF) DELETE FROM "type_UUID" WHERE "i" IN (42, 43); QUERY PLAN ---------------------------------------------------------------------------- Delete on public."type_UUID" -> Foreign Delete on public."type_UUID" SQLite query: DELETE FROM main."type_UUID" WHERE (`i` IN (42, 43)) (3 rows) --Testcase 092: DELETE FROM "type_UUID" WHERE "i" IN (42, 43); --Testcase 093: INSERT INTO "type_UUID" ("i", "u") VALUES (44, NULL); --Testcase 094: SELECT * FROM "type_UUID+"; i | u | t | l ----+--------------------------------------+------+---- 41 | b0eebc99-9c0b-4ef8-bb6d-6bb900000a15 | text | 36 44 | | null | (2 rows) --Testcase 095: SELECT * FROM "type_UUID+" WHERE "u" IS NULL; i | u | t | l ----+---+------+--- 44 | | null | (1 row) --Testcase 096: SELECT * FROM "type_UUID+" WHERE "u" IS NOT NULL; i | u | t | l ----+--------------------------------------+------+---- 41 | b0eebc99-9c0b-4ef8-bb6d-6bb900000a15 | text | 36 (1 row) --Testcase 097: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_UUID+" WHERE "u" IS NULL; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public."type_UUID+" Output: i, u, t, l SQLite query: SELECT `i`, sqlite_fdw_uuid_blob(`u`), `t`, `l` FROM main."type_UUID+" WHERE ((sqlite_fdw_uuid_blob(`u`) IS NULL)) (3 rows) --Testcase 098: EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM "type_UUID+" WHERE "u" IS NOT NULL; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public."type_UUID+" Output: i, u, t, l SQLite query: SELECT `i`, sqlite_fdw_uuid_blob(`u`), `t`, `l` FROM main."type_UUID+" WHERE ((sqlite_fdw_uuid_blob(`u`) IS NOT NULL)) (3 rows) --Testcase 100: CREATE FOREIGN TABLE "type_UUIDpk" (col uuid OPTIONS (key 'true')) SERVER sqlite_svr; --Testcase 101: ALTER FOREIGN TABLE "type_UUIDpk" ALTER COLUMN col OPTIONS (ADD column_type 'TEXT'); --Testcase 102: INSERT INTO "type_UUIDpk" VALUES ('{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}'); --Testcase 103: INSERT INTO "type_UUIDpk" VALUES ('{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); --Testcase 104: SELECT * FROM "type_UUIDpk"; col -------------------------------------- a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 (2 rows) --Testcase 105: ERR - primary key INSERT INTO "type_UUIDpk" VALUES ('{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); ERROR: Failed to execute remote SQL HINT: SQLite error 'UNIQUE constraint failed: type_UUIDpk.col', SQLite result code 19 CONTEXT: SQL query: INSERT INTO main."type_UUIDpk"(`col`) VALUES (?) --Testcase 106: ALTER FOREIGN TABLE "type_UUIDpk" ALTER COLUMN col OPTIONS (SET column_type 'BLOB'); --Testcase 107: NO ERR, but the same semantics! INSERT INTO "type_UUIDpk" VALUES ('{b0eebc99-9c0b4ef8-bb6d6bb9-bd380a12}'); --Testcase 108: SELECT * FROM "type_UUIDpk"; col -------------------------------------- a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12 (3 rows) --Testcase 109: DELETE FROM "type_UUIDpk"; --Testcase 200: DROP EXTENSION sqlite_fdw CASCADE; NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to server sqlite_svr drop cascades to foreign table "type_UUID" drop cascades to foreign table "type_UUID+" drop cascades to foreign table "type_UUIDpk" drop cascades to server sqlite2