What is the postgresql equivalent of DUMP function in Oracle?-CodePudding

The dump function in Oracle displays the internal representation of data:

DUMP returns a VARCHAR2 value containing the data type code, length in bytes, and internal representation of expr

Fore example:

SELECT DUMP(cast(1 as number ))
  2    FROM DUAL;

DUMP(CAST(1ASNUMBER))
--------------------------------------------------------------------------------
Typ=2 Len=2: 193,2

SQL> SELECT DUMP(cast(1.000001 as number ))
  2    FROM DUAL;

DUMP(CAST(1.000001ASNUMBER))
--------------------------------------------------------------------------------
Typ=2 Len=5: 193,2,1,1,2

It shows that the first 1 uses 2 byte for storing and the second example uses 5 bytes for storing.

I suppose the similar function in PostgreSQL is pg_typeof but it returns only the type name without information about byte usage:

SELECT pg_typeof(33);

pg_typeof

integer (1 row)

Does anybody know if there is an equivalent function in PostgreSQL?

CodePudding user response：

I don't speak PostgreSQL.

However, Oracle functionality page says that there's Orafce which

implements in Postgres some of the functions from the Oracle database that are missing (or behaving differently)

It, furthermore, mentions the dump function

dump (anyexpr [, int]): Returns a text value that includes the datatype code, the length in bytes, and the internal representation of the expression

One of examples looks like this:

postgres=# select pg_catalog.dump('Pavel Stehule',10);
                                  dump                                   
-------------------------------------------------------------------------
 Typ=25 Len=17: 68,0,0,0,80,97,118,101,108,32,83,116,101,104,117,108,101
(1 row)

To me, it looks like Oracle's dump:

SQL> select dump('Pavel Stehule') result from dual;

RESULT
--------------------------------------------------------------
Typ=96 Len=13: 80,97,118,101,108,32,83,116,101,104,117,108,101

SQL>

I presume you'll have to visit GitHub and install the package to see whether you can use it or not.

CodePudding user response：

It is not a complete equivalent, but if you want to figure out the byte values used to encode a string in PostgreSQL, you can simply cast the value to bytea, which will give you the bytes in hexadecimal:

SELECT CAST ('schön' AS bytea);

This will work for strings, but not for numbers.