Getting php array key and value from postgresql function parameter to update database

Question

I am trying to send an array of value value pairs to the postgresql function as a parameter. The array structure 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" 
}

And the function takes a string array parameter like this

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 to get the array sent to p_doc_attribs inside my function. As for a specific key name, I need to insert the desired value into the table. Accordingly, the following request must 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 || '''';

The attribute identifier will contain the key, and the value_ attribute for the specific case will be the value obtained from the p_doc_attribs array.

Answer 1

, json_encode , :

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)

:

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

.

, PLV8,

EDIT FOR V 9.1.x

, , , :

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

PHP postgres array postgresql , , , ,

p_doc_attribs[3]

p_doc_attribs['PREFLOC']