Re: Query on postgres_fdw extension

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