Обсуждение: PL/pgGRESQL, SHA, BYTEA - Creating SHA1 hash for Bytea Value in stored procedure

Hi!

I have the following scenario:
    Table:
        Fil_no    BigSerial
        Fil_Name    varchar
        Fil_Data    Bytea
        Fil_HASH    varchar

    Trigger:
        Before Insert  (in Pseudo Code)
            ...
            vchSHA = SHA(Fil_DATA)
            NEW.Fil_HASH = vchSHA
            ...

I tried to find a function that calculates the SHA (preference is sha-256),
but could not find anything for PostGRE. However, I want to implement the
calculation within the DB, for several reasons.

Has anyone an idea on how or any pointer into the right direction to
accomplish the above task?

Thanks already in advance!

-Enrico

On Fri, Jul 08, 2005 at 04:43:41PM -0500, Enrico Riedel wrote:
>
> I tried to find a function that calculates the SHA (preference is sha-256),
> but could not find anything for PostGRE. However, I want to implement the
> calculation within the DB, for several reasons.

See the contrib/pgcrypto module.

BTW, it's PostgreSQL or Postgres, not PostGRE.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Enrico Riedel wrote:

> Has anyone an idea on how or any pointer into the right direction to
> accomplish the above task?
>
> Thanks already in advance!

If you don't mind having plpythonu installed in your database, a lot of this
sort of thing becomes trivial:

CREATE OR REPLACE FUNCTION sha1(text) RETURNS char(40) AS '
    import sha
    return sha.new(args[0]).hexdigest()
' LANGUAGE plpythonu IMMUTABLE RETURNS NULL ON NULL INPUT;


--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/