cloudberry icon indicating copy to clipboard operation
cloudberry copied to clipboard

Enable INSERT/UPDATE/DELETE for postgres_fdw

Open my-ship-it opened this issue 2 years ago • 0 comments

Enhancement

CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
        EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
    "C 1" int NOT NULL,
    c2 int NOT NULL,
    c3 text,
    c4 timestamptz,
    c5 timestamp,
    c6 varchar(10),
    c7 char(10),
    c8 user_enum,
    CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);

CREATE FOREIGN TABLE ft2 (
    c1 int NOT NULL,
    c2 int NOT NULL,
    cx int,
    c3 text,
    c4 timestamptz,
    c5 timestamp,
    c6 varchar(10),
    c7 char(10) default 'ft2',
    c8 user_enum
) SERVER loopback;

ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');

INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;

Then, INSERT will fail. Need to investigate how to support INSERT/UPDATE/DELETE for postgres_fdw

my-ship-it avatar Jul 24 '23 07:07 my-ship-it