What is the postgresql equivalent of DUMP function in Oracle?

user_5681468 0
Viewed 193

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?

postgresql oracle
2 Answers

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.

user_90979060

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.

user_64643080
Related