Retrieve the PHP array keys and values ​​from the PostgreSQL function parameters for database updates

By Postgresql

I am trying to send a set of key-value pairs to a postgresql function as a parameter. The structure of the array is as follows – 

array( 10) {
 ["OWNER"]=> string(3) "ERP" 
 ["SOURCE"]=> string(7) "Unknown" 
 ["PRIORITY"]=> string(6) "Medium" 
 ["PREFLOC"]=> string(5) "Dhaka" 
 ["PROBABLE"]=> string(2) "50" 
 ["MAXSIZE "]=> string(4) "1000" 
 ["MINSIZE"]=> string(4) "2000" 
 ["INTAREA"]=> string(14) "Dhaka, Gulshan" < br /> ["CVALPRF"]=> string(5) "Great" 
 ["OPPAMOUNT"]=> string(3) "200" 
}

The function accepts this 

CREATE OR REPLACE FUNCTION 
document.update_doc_attrib_on_opportunity(p_org_id numeric, p_target_doc_code numeric character varying, 
p_target_doc_no, p_doc_attribs character varying[] )

Now I want to send the array to p_doc_attribs in my function. For a specific key name, I need to insert the required value in the table.
The following query needs to be updated accordingly – 

'UPDATE use_doc_attribute
 SET attrib_value = CASE WHEN attrib_code =''PREFLOC'' THEN''' || p_preferred_location ||'''
 WHEN attrib_code =''PRIORITY'' THEN''' || p_priority ||'''
 WHEN attrib_code =''PROBABLE'' THEN''' || p_probability ||'''
 WHEN attrib_code =''SOURCE'' THEN''' || p_source ||'''
 WHEN attrib_code =''MAXSIZE'' THEN''' || p_max_size ||'''
 WHEN attrib_code = ' 'MINSIZE'' THEN''' || p_min_size ||'''
 WHEN attrib_code =''INTAREA'' THEN''' || p_interested_areas ||'''
 WHEN attrib_code =''CVALPRF '' THEN''' || p_client_value_profile ||'''
 ELSE attrib_value
 END
 WHERE org_id = '|| p_org_id ||'
 AND document_no =' || p_target_doc_no ||'
 AND document_code =''' || p_target_doc_code ||'''';

attrib_code will contain the key, and the attrib_value in a specific case will be the value retrieved from the p_doc_attribs array.

I don’t I am an expert in this area, but you can json_encode the array and pass it to your function, because you have to make the following changes to your function: 

 CREATE OR REPLACE FUNCTION document.update_doc_attrib_on_opportunity(
 p_org_id numeric, 
 p_target_doc_code character varying, 
 p_target_doc_no numeric, 
 p_doc_attribs JSON)

Your query may look like this Of:

'UPDATE use_doc_attribute
 SET attrib_value = CASE WHEN attrib_code =''PREFLOC'' THEN''' || p_doc_attribs['PREFLOC'] || ' ''
 WHEN attrib_code =''PRIORITY'' THEN''' || p_doc_attribs['PRIORITY'] ||'''
 WHEN attrib_code =''PROBABLE'' THEN''' || p_doc_attribs ['PROBABLE'] ||'''
 WHEN attrib_code =''SOURCE'' THEN''' || p_doc_attribs['SOURCE'] ||'''
 WHEN attrib_code =''MAXSIZE' 'THEN''' || p_doc_attribs['MAXSIZE'] ||'''
 WHEN attrib_code =''MINSIZE'' THEN''' || p_doc_attribs['MINSIZE'] ||'''
 WHEN attrib_code =''INTARE A'' THEN''' || p_doc_attribs['INTAREA']||'''
 WHEN attrib_code =''CVALPRF'' THEN''' || p_doc_attribs['CVALPRF'] ||'''< br /> ELSE attrib_value
 END
 WHERE org_id = '|| p_org_id ||'
 AND document_no =' || p_target_doc_no ||'
 AND document_code =''' || p_target_doc_code ||'''';

You can check the following link for more examples of this type.

Edit

I think you need Install the PLV8 extension, you can find more information here

Edit V 9.1.x.

You can create and pass an index array, for example, create array is similar to the following:

p> 

[
 "ERP",
 "Unknown",
 "Medium",
 "Dhaka",
 " 50",
 "1000",
 "2000",
 "Dhaka, Gulshan",
 "Great",
 "200"
]< /pre> 
Then use the PHP array to postgres array function to convert and pass the array to the postgresql function, and change the array value used in the query to be index-based instead of key-based, as shown below:
 < p>p_doc_attribs [3]
 
Replace
 
p_doc_attribs ['PREFLOC']

I tried to combine a set of key-value pairs Send to the postgresql function as a parameter. The structure of the array is as follows – 

array(10) {
 ["OWNER" ]=> string(3) "ERP" 
 ["SOURCE"]=> string(7) "Unknown" 
 ["PRIORITY"]=> string(6) "Medium" 
 ["PREFLOC"]=> string(5) "Dhaka" 
 ["PROBABLE"]=> string(2) "50" 
 ["MAXSIZE"]=> string(4) "1000" 
 ["MINSIZE"]=> string(4) "2000" 
 ["INTAREA"]=> string(14) "Dhaka, Gulshan" 
 ["CVALPRF"]=> string (5) "Great" 
 ["OPPAMOUNT"]=> string(3) "200" 
}

The function accepts such a string array parameter

CREATE OR REPLACE FUNCTION 
document.update_doc_attrib_on_opportunity(p_org_id numeric, p_target_doc_code character varying, 
p_target_doc_no numeric, p_doc_attribs character varying[])

Now I want Send the array to p_doc_attribs in my function. For a specific key name, I need to insert the required value in the table.
The following query needs to be updated accordingly – 

'UPDATE use_doc_attribute
 SET attrib_value = CASE WHEN attrib_code =''PREFLOC'' THEN''' || p_preferred_location ||'''
 WHEN attrib_code =''PRIORITY'' THEN''' || p_priority ||'''
 WHEN attrib_code =''PRO BABLE'' THEN''' || p_probability ||'''
 WHEN attrib_code =''SOURCE'' THEN''' || p_source ||'''
 WHEN attrib_code =''MAXSIZE' 'THEN''' || p_max_size ||'''
 WHEN attrib_code =''MINSIZE'' THEN''' || p_min_size ||'''
 WHEN attrib_code =''INTAREA'' THEN ''' || p_interested_areas ||'''
 WHEN attrib_code =''CVALPRF'' THEN''' || p_client_value_profile ||'''
 ELSE attrib_value
 END
 WHERE org_id = '|| p_org_id ||'
 AND document_no =' || p_target_doc_no ||'
 AND document_code =''' || p_target_doc_code ||'''';

attrib_code will contain the key, and the attrib_value of the specific case will be the value retrieved from the p_doc_attribs array.

I am not an expert in this area, but you can Json_encode the array and pass it to your function, because you must make the following changes to your function: 

CREATE OR REPLACE FUNCTION document.update_doc_attrib_on_opportunity(
 p_org_id numeric,  p_target_doc_code character varying, 
 p_target_doc_no numeric, 
 p_doc_attribs JSON)

Your query may look like this:

 'UPDATE use_doc_attribute
 SET attrib_value = CASE WHEN attrib_code =''PREFLOC'' THEN''' || p_doc_attribs['PREFLOC'] ||'''
 WHEN attrib_code =''PRIORITY'' THEN ' '' || p_doc_attribs['PRIORITY'] ||'''
 WHEN attrib_code =''PROBABLE'' THEN''' || p_doc_attribs['PROBABLE'] ||'''
 WHEN attrib_code =''SOURCE'' THEN''' || p_doc_attribs['SOURCE'] ||'''
 WHEN attrib_code =''MAXSIZE'' THEN''' || p_doc_attribs['MAXSIZE'] || ' ''
 WHEN attrib_code =''MINSIZE'' THEN''' || p_doc_attribs['MINSIZE'] ||'''
 WHEN attrib_code =''INTAREA'' THEN''' || p_doc_attribs ['INTAREA']||'''
 WHEN attrib_code =''CVALPRF'' THEN''' || p_doc_attribs['CVALPRF'] ||'''
 ELSE attrib_value 
 END
 WHERE org_id = '|| p_org_id ||'
 AND document_no =' || p_target_doc_no ||'
 AND document_code =''' || p_target_doc_code ||'' '';

You can check the following link for more such examples.

Edit

I think you need to install the PLV8 extension for this to work, you More information can be found here

Edit V 9.1.x.

You can create and pass an index array, for example, create array is similar to the following:

[
 "ERP",
 "Unknown",
 "Medium",
 "Dhaka",
 "50",
 "1000",
 "2000",
 "Dhaka, Gulshan",
 "Great",
 "200"
]

Then use the PHP array to postgres array function to convert and pass the array to the postgresql function, and change the array value used in the query to be index-based instead of key-based, as shown below:

p_doc_attribs [3 ]

Replace

p_doc_attribs ['PREFLOC']

, , , , , , , , , , ,

Leave a Comment

Your email address will not be published.