CODEWITHSUNDEEP
sqlpostgresqltransactionscommit
fawzib
fawzib

Reputation: 842

postgresql function not found

Trying to run sha256 function

CREATE EXTENSION pgcrypto;
CREATE OR REPLACE FUNCTION sha256(bytea) returns text AS $$
    SELECT encode(digest($1, 'sha256'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;

WITH
tab_email as        (SELECT '[email protected]'::text as email FROM tmp),
INSERT INTO users (email, password) VALUES ((SELECT email FROM tab_email), sha256('mypass'));

i got this error

ERROR: function sha256(text) does not exist

Upvotes: 2

Views: 4749

Answers (3)

dblpanther
dblpanther

Reputation: 11

If you create this function in redshift, you will be able to your f_sha256

CREATE OR REPLACE FUNCTION f_sha256 (mes VARCHAR)
    returns VARCHAR
    STABLE AS $$
    import hashlib
    return hashlib.sha256(mes).hexdigest()
    $$ language plpythonu;

Upvotes: -1

Seamus Abshere
Seamus Abshere

Reputation: 8516

It's because Postgres's built-in sha256 function takes a bytea argument:

citus=> \df+ sha256
                                                                               List of functions
   Schema   |  Name  | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code  | Description
------------+--------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+--------------+--------------
 pg_catalog | sha256 | bytea            | bytea               | func | immutable  | safe     | postgres | invoker  |                   | internal | sha256_bytea | SHA-256 hash
(1 row)

So just cast to ::bytea first.

citus=> select encode(sha256('a'::bytea), 'hex');
                              encode
------------------------------------------------------------------
 ca978112ca1bbdcafac231b39a23dc4da786eff8147c4e72b9807785afee48bb
(1 row)

Upvotes: 8

fawzib
fawzib

Reputation: 842

I ended up using

encode(digest(password, 'sha256'), 'hex') from tab_password)

Upvotes: 0

Related Questions