JSON to BYTEA

SOLVED
Highlighted
Level 2 Adventurer

JSON to BYTEA

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;

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
EDB Team Member

Re: JSON to BYTEA

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

View solution in original post

4 REPLIES 4
Highlighted
Level 3 Adventurer

Re: JSON to BYTEA

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. 

Highlighted
EDB Team Member

Re: JSON to BYTEA

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

View solution in original post

Highlighted
Level 2 Adventurer

Re: JSON to BYTEA

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 

Highlighted
Level 3 Adventurer

Re: JSON to BYTEA

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.

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks