JSON to BYTEA

[neiljo10]

Hello,

I want my code function to convert the JSON data to BYTEA and then inserts into the table. Following is the code,

CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
DECLARE
    v1 "TBL1"."COL1"%TYPE;
    v2 "TBL1"."COL2"%TYPE;
BEGIN
v1 := utl_raw.cast_to_raw('{
 "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""              
     }');

 v2 := utl_raw.cast_to_raw('{
    "APPLICATION": {
   "MEMORY": {
   "OPTIONS" :{
                "SOMETHING" : "SOMETHING",
                "format" : "SOMETHING",
                "System" : "",
                "IP" : "",
                "Port" : "",
                "template" : "",
                "Path" : "" ,
                "Name" : "QUEUE",
                "URL" : ""  
                    }');

 INSERT INTO "TBL1" ("SN","COL1","COL2") values(1,v1, v2);
END;
$$

LANGUAGE 'plpgsql';
COMMIT;

[Ranjan]

Hi  neiljo10,

Please provide us with below-required information.

1. Which EPAS version you are using.

2. Is the function code you have pasted executing successfully, if not can you paste an error you are facing.

3. Table structure in which trying to insert data after manipulation. 

[kapil · Accepted solution]

Hi neiljo10,

Could you please remove the utl_raw.cast_to_raw function and execute the below function, we have tested the same and it works in our local environment.

Please find the attached function for your reference.

edb=# CREATE OR REPLACE FUNCTION INS_BLOB() RETURNS VOID AS $$
edb$# BEGIN
edb$# INSERT INTO "TBL1" ("SN","COL1","COL2")
edb$# values(1,
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}',
edb$# '{
edb$# "APPLICATION": {
edb$# "MEMORY": {
edb$# "OPTIONS" :{
edb$# "SOMETHING" : "SOMETHING",
edb$# "format" : "SOMETHING",
edb$# "System" : "",
edb$# "IP" : "",
edb$# "Port" : "",
edb$# "template" : "",
edb$# "Path" : "" ,
edb$# "Name" : "QUEUE",
edb$# "URL" : ""
edb$# }}}}');
edb$# END;
edb$# $$
edb-# LANGUAGE plpgsql;
CREATE FUNCTION
edb=# COMMIT;
COMMIT

[neiljo10]

Hi,

It is actually working fine.

But what I wanna confirmis that wether the data getting stored in BYTEA column is being converted to BYTEA data type.

@kapil  @Ranjan 

[Ranjan]

Hi neiljo10,

Bytea data type is accepting data to get stored means it is bytea type data otherwise it would have through error while storing data in bytea type column.

You will see the data stored in bytea column which is in binary format.