postgresql function not found

Question

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

INSERT INTO users SELECT email, sha256('mypass') FROM tab_email; — Lukasz Szozda
– Lukasz Szozda, Commented Nov 24, 2015 at 20:30
@lad2025 That's better, but does not explain the error. Which is odd. — Craig Ringer
– Craig Ringer, Commented Nov 25, 2015 at 1:15
What does \df *.sha256 show? How about SELECT sha256('1') ? — Craig Ringer
– Craig Ringer, Commented Nov 25, 2015 at 1:15

Seamus Abshere · Accepted Answer · 2019-03-28 18:40:27Z

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)

fawzib · Accepted Answer · 2015-11-25 15:56:03Z

0

I ended up using

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

answered Nov 25, 2015 at 15:56

fawzib

8722 gold badges11 silver badges24 bronze badges

Comments

dblpanther · Accepted Answer · 2021-12-13 06:35:14Z

-1

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;

edited Dec 13, 2021 at 6:35

answered May 26, 2020 at 18:59

dblpanther

113 bronze badges

1 Comment

the Tin Man Over a year ago

See "Explaining entirely code-based answers". While this might be technically correct it doesn't explain why it solves the problem or should be the selected answer. We should educate in addition to help solve the problem.

Collectives™ on Stack Overflow

postgresql function not found

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related