Insert JSON Data to BYTEA Column in PostgreSQL

jsonpostgresql

So, I came across the function script which is converting JSON data to BYTEA and then insert as a record in the table in a BYTEA column. (As I assumed what the code is doing)

In Oracle the function utl_raw.cast_to_raw converts the data to blob data and records the data in the table in Blob column. Giving the following output message, "anonymous block completed"

The 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;

[SOLVED] Edit: So generally the problem was nothing. So all I had to remove was the utl_raw.cast_to_raw line and execute it as it is. No errors, no problems. THANKS

Best Answer

There is no reason to convert anything, just insert the JSON strings (after making them valid JSON):

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

You also don't need PL/pgSQL for this. A language sql function would be enough (you would need to remove the begin and end though)