Postgres encode() function doesn't work with a function as an argument

Question

I'm trying to use Postgresql encode() function and put some other functions as it's arguments. I get errors, and I can't understand why.

I am using Postgres 9.6.14 on Windows.

1. This works fine and returns 698d51a19d8a121ce581499d7b701668

select md5('111'); 

2. This also works fine and returns some value

select encode('698d51a19d8a121ce581499d7b701668', 'base64');

3. But this one, which combines the first 2, doesn't work and returns "ERROR: function encode(text, unknown) does not exist"

select encode(md5('111'), 'base64');

4. This also doesn't work and returns the same error

select encode(concat('1', '11'), 'base64');

5. I thought that there is a rule which prevents to use one function inside another, but the following one works fine and returns the same value as the first request here, as expected.

select md5(concat('1', '11'))

So what's the issue with requests number 3 and 4 and encode() function overall?

Answer 1

Look at the definition of encode:

\df encode
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type 
------------+--------+------------------+---------------------+------
 pg_catalog | encode | text             | bytea, text         | func
(1 row)

The first argument has to be bytea, that is a binary byte string.

Your first two queries work because string literals are of type unknown, which can be cast to bytea implicitly.

In the queries that do not work, you are using the functions md5 and concat, which both have text as a result type. Now there is no implicit cast between text and bytea, hence the error message.

To make that work, you have to introduce an explicit type cast:

select encode(CAST(md5('111') AS bytea), 'base64');