On Thu, 2022-01-20 at 15:59 +0000, Duarte Carreira wrote:
> I got here after encountering the same difficulty, although on a much more mundane scenario.
>
> I'm used to fdw on a read-only basis. I was just inserting a new record on a foreign table
> and got blocked... and after much searching got here.
>
> As far as I can see it is impossible to use fdw to insert records on 99% of tables,
> since all have some kind of primary sequential key.
Yes, this is tricky. You could use something like this:
CREATE TABLE local (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
data text
);
CREATE FOREIGN TABLE remote (id bigint NOT NULL, data text)
SERVER whatever OPTIONS (table_name 'local');
CREATE FOREIGN TABLE remote_noid (data text)
SERVER whatever OPTIONS (table_name 'local');
CREATE VIEW v_remote AS SELECT * FROM remote;
CREATE FUNCTION ins_trig() RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
INSERT INTO remote_noid (data) VALUES (NEW.data);
RETURN NEW;
END;$$;
CREATE TRIGGER ins_trig INSTEAD OF INSERT ON v_remote
FOR EACH ROW EXECUTE FUNCTION ins_trig();
INSERT INTO v_remote (data) VALUES ('something');
SELECT * FROM v_remote;
id │ data
════╪═══════════
1 │ something
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
