Hmmm... I don't think a view or trigger are necessary.
If we just create the 2 foreign tables, one complete and one without id, you can simply insert into the table without id and it will work fine.
To select and show data, you use the "complete" table that has the id column.
No need for trigger and view. If I understood correctly.
I have this 2 table setup working.
It's a workaround that quickly escalates out of hand though... with little added value.
Thanks.
Laurenz Albe <laurenz.albe@xxxxxxxxxxx> escreveu no dia sexta, 21/01/2022 à(s) 13:59:
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
