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