On Wed, Aug 2, 2017 at 4:25 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Aug 2, 2017 at 3:42 PM, Peter Koukoulis <pkoukou...@gmail.com>
> wrote:
>
>>
>> SQL> select dbms_sqlhash.gethash('select x,y from test1',2) as md5_value
>> from dual;
>>
>> MD5_VALUE
>> ------------------------------------------------------------
>> --------------------
>> 9FDA7FA725B783172CA371DA04AD5754
>>
>>
>> Can I do something similar in PostgreSQL ?
>>
>>
> Similar.
>
> SELECT md5(string_agg(vals::text, ''))
> FROM ( VALUES (1, 2), (2, 3) ) vals (x, y)
>
>
>
That is going to build up the entire string in memory, so will fail if the
text representation of the entire table doesn't fit in 1GB.
I don't see any feature in PostgreSQL for calculating hashes over streaming
data. But it wouldn't be too hard to create something in plperl, for
example, to do that. You would have to make sure the query always returns
rows in the same order (I don't know if Oracle's function handles that for
you) and that things like datestyle and timezone don't cause differences.
You could use something like:
\copy (select * from blah order by something) to program 'md5sum' binary
but I don't know how you would get the output back into your program once
it shows up on your screen.
Cheers,
Jeff
