Re: md5 of table

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