php postgresql 参数,从postgresql函数参数中检索php数组键和值以进行数据库更新-CSDN博客

我试图将一组键值对发送到postgresql函数作为参数.阵列的结构如下 –

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" }

函数接受这样的字符串数组参数

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[])

现在我想把数组发送到我的函数中的p_doc_attribs.对于特定的键名,我需要在表中插入所需的值.

以下查询需要相应更新 –

"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将包含密钥,特定情况的attrib_value将是从p_doc_attribs数组中检索的值.

解决方法:

我不是这方面的专家,但您可以对数组进行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 || """";

您可以查看以下link以获取更多此类示例.

编辑

我认为为此工作你需要安装PLV8扩展,你可以找到更多信息here

编辑V 9.1.x.

您可以创建并传递索引数组,例如,create array类似于以下内容：

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

然后使用PHP array to postgres array函数转换并将该数组传递给postgresql函数,并将查询中使用的数组值更改为基于索引而不是基于键,如下所示：

p_doc_attribs [3]

代替

p_doc_attribs [ ‘PREFLOC’]

来源：https://www.icode9.com/content-2-305051.html