Hello,
I'm learning now something about foreign tables in PG 9.3. I wonder if
there is a clean way to use a sequence on the remote side, so that an
"insert into remote_table values ([data not including id]) returning
id" would ask the remote server to generate a new value for id.
As it stands now defaults are evaluated client-side and a column with
no default is treated as defaulting to null (as per docs): I see from
the logs that explicit nulls are sent to the server even if not
requested by the insert, or if "default" is specified.
I've worked around that using a trigger on the remote table to
reinstate the defaults, something along the line of:
create or replace function ... returning trigger
begin
if new.id is null then new.id = nextval('seqname'::regclass); end if;
if new.cr_date is null then new.cr_date = now(); end if;
return new;
end
create trigger ... before insert for each row...
but I wonder if there is a more idiomatic way to do that. Using a
sequence on the remote side instead of the local seems a basic use
case and local sequences are not an option if more than one database
have a foreign table on the same physical table.
Thank you very much
-- Daniele