Retrieving php array key and value from postgresql function parameter for database update

Question

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

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 accepts 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. Like for specific key name I need to insert the desired value in 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 || '''';

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

Community · Accepted Answer · 2017-05-23 12:29:45Z

1

I am not expert at this but you can json_encode the array and pass it to your function, for that you will have to make 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)

and your query could be 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 following link for more such example.

EDIT

I think for this to work you will need PLV8 extension installed you can find more information here

EDIT FOR V 9.1.x

You can create and pass indexed array for example, create array similar to following:

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

then use PHP array to postgres array function to convert and pass that array to postgresql function and change array values used in query to index based instead of key based as follows:

p_doc_attribs[3]

instead of

p_doc_attribs['PREFLOC']

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Sep 15, 2015 at 6:51

Dharmesh Patel

1,8911 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shuvro Over a year ago

Thank you, I will try this out shortly and let you know if it works.

Shuvro Over a year ago

JSON data type was introduced from PostgreSQL 9.2 but I'm using 9.1.14 which can not be upgraded right now to latest version. So, I guess passing the array as JSON won't work for me at this moment. Can you suggest any other workarounds?

Shuvro Over a year ago

The edited portion helped and worked perfectly. Thank you

Collectives™ on Stack Overflow

Retrieving php array key and value from postgresql function parameter for database update

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related