Geek asks and answers

Md5 () works with literal but not column data

When testing the PostgreSQL function, md5()I noticed a very strange behavior:

Works as expected

SELECT md5('abc')
--"900150983cd24fb0d6963f7d28e17f72"

But using the md5 () function in the request:

SELECT request_id, md5(request_id)
FROM Request
ORDER BY request_id

leads to this error:

ERROR:  function md5(integer) does not exist
LINE 1: SELECT request_id, md5(request_id)
                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function md5(integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 20

How does a function not exist if it worked in the first request? What am I doing wrong; What is the correct way to use md5()SELECT in a query?

+5
source share
4 answers

The function expects text as a parameter. Drop this:

SELECT request_id, md5(request_id::text)
FROM Request
ORDER BY request_id

A function named md5 that takes an integer parameter does not exist, but you can create it:

create function md5(integer)
returns text as $$

select md5($1::text);

$$ language sql immutable;

Then for md5 there will be 3 signatures:

=> \df md5
                          List of functions
   Schema   | Name | Result data type | Argument data types |  Type  
------------+------+------------------+---------------------+--------
 pg_catalog | md5  | text             | bytea               | normal
 pg_catalog | md5  | text             | text                | normal
 public     | md5  | text             | integer             | normal

, md5 , . , md5, bytea:

select md5(('\x' || right('0000000' || to_hex(200), 8))::bytea);
               md5                
----------------------------------
 b7b436d004c1cc0501bee9e296d2eaa4

:

create or replace function md5(integer)
returns text as $$

select md5(('\x' || right('0000000' || to_hex($1), 8))::bytea);

$$ language sql immutable;
+10

md5 . , , . , :

pseudo_encrypt, - PostgreSQL. , md5 , () .

, . , , , ..

+3

; md5(), . CAST() , :

SELECT request_id, md5(CAST(request_id AS TEXT))
FROM Request
ORDER BY request_id

--1;"c4ca4238a0b923820dcc509a6f75849b"
--2;"c81e728d9d4c2f636f067f89cc14862c"
--etc
0

, , .

. CPU CRC C. , , .

"" 32- .

If you know how to create C functions (not trivial ones), it will be a simple task to see how to use CRC.

0
source

More articles:


All Articles