Re: Query on postgres_fdw extension

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(dot)albe(at)cybertec(dot)at> 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
>
>