2011/9/1 Sim Zacks <sim@compulab.co.il>:
>
>> OP:
>>>
>>> I thought about using dblink and the EXCEPT query, but then I need to
>>> know the field list of each query result, which is a pain in the butt.
>>
>> That is not correct. As long as the table definitions are precisely
>> the same, you can move records across dblink without specifying
>> fields. You do this by using record type for the composite which
>> dblink sends across as text.
>>
>> merlin
>
> Do you have a quick example? This is what I have tried:
>
> select * from tbla
> except
> select * from dblink('host=dbhost dbname=otherdb user=myuser
> password=mypwd'::text, 'select * from tbla')
>
> The error I get back is:
> ERROR: a column definition list is required for functions returning
> "record"
sure:
select tbla from tbla
except
select t::tbla from dblink('host=dbhost dbname=otherdb user=myuser
password=mypwd'::text, 'select tbla::text from tbla') R(t text);
there's a bunch of ways to do that -- you can also do the md5 on the
remote side so you can just send the digests.
select * from tbla
except
select (t::tbla).* from dblink('host=dbhost dbname=otherdb user=myuser
password=mypwd'::text, 'select tbla::text from tbla') R(t text);
should also work.
This *might* work -- I didn't try. It's been a while since I've used
stock dblink.
select * from tbla
except
select (t).* from dblink('host=dbhost dbname=otherdb user=myuser
password=mypwd'::text, 'select tbla from tbla') R(t tbla);
merlin