Postgresql error: function "json_to_recordset(text) doesn't exist"

Question

I create this function:

-- Function: public.proc_rebate2reachrebateinsert(text)

-- DROP FUNCTION public.proc_rebate2reachrebateinsert(text);

CREATE OR REPLACE FUNCTION public.proc_rebate2reachrebateinsert(paramstr text)
  RETURNS void AS
$BODY$

DECLARE
v_sql1 text;
v_sql2 text;
v_sql3 text;
v_sql4 text;
s_contract_no text;
v_contract_no character varying;
v_line_no integer; 
v_history_no integer;                                   
v_f_sin integer;                                        
v_p_typeid integer;                                 
v_compare_typeid integer;   
v_val_typeid integer;                                       
v_goal_val numeric;
v_count_typeid integer; 
v_f_pb_ignore integer;
v_all_products integer;
v_judge_typeid integer;                                                         
v_ins_user_id integer;
v_upd_user_id integer;
v_upd_user_name character varying;
v_reach_condition_array text;
v_dept_category_jan_array text;
v_sku_appoint text;
v_series_appoint text;

in_obj text;
objjson json;
insertjson json;
begin

    insertJson=paramStr::json;
    s_contract_no=(select cast(json_extract_path(insertJson,'contract_no') as text));
    v_line_no=to_number((select cast(json_extract_path(insertJson,'line_no') as text)),'000000000000000000');
    v_history_no=to_number((select cast(json_extract_path(insertJson,'history_no') as text)),'000000000000000000');
    v_p_typeid=to_number((select cast(json_extract_path(insertJson,'p_typeid') as text)),'000000000000000000');
    v_compare_typeid=to_number((select cast(json_extract_path(insertJson,'compare_typeid') as text)),'000000000000000000');
    v_val_typeid=to_number((select cast(json_extract_path(insertJson,'val_typeid') as text)),'000000000000000000');                                     
    v_goal_val=to_number((select cast(json_extract_path(insertJson,'goal_val') as text)),'000000000000000000'); 
    v_count_typeid=to_number((select cast(json_extract_path(insertJson,'count_typeid') as text)),'000000000000000000');
    v_judge_typeid=to_number((select cast(json_extract_path(insertJson,'judge_typeid') as text)),'000000000000000000'); 
    select json_extract_path(insertJson,'reach_condition_array') into v_reach_condition_array;
    if v_p_typeid='1' then
        select json_extract_path(insertJson,'dept_category_jan_array') into v_dept_category_jan_array;
    elsif v_p_typeid='2' then 
        select json_extract_path(insertJson,'sku_appoint') into v_sku_appoint;
    elsif v_p_typeid='3' then 
        select json_extract_path(insertJson,'series_appoint') into v_series_appoint;    
    end if;  
v_ins_user_id=to_number((select cast(json_extract_path(insertJson,'ins_user_id') as text)),'000000000000000000');
    v_upd_user_id=to_number((select cast(json_extract_path(insertJson,'upd_user_id') as text)),'000000000000000000');
    v_upd_user_name=replace((select cast(json_extract_path(insertJson,'upd_user_name') as character varying)),'"','');

    if (s_contract_no <> '""') then
        v_contract_no=replace((select cast(json_extract_path(insertJson,'contract_no') as character varying)),'"','');
        v_history_no=to_number((select cast(json_extract_path(insertJson,'history_no') as text)),'000000000000000000');

        if exists(select * from public.rebatesys_mstcontract  where contract_no=v_contract_no and history_no=v_history_no and line_no=v_line_no) then
            v_f_sin=(select f_sin from public.rebatesys_00mstcontract  where contract_no=v_contract_no and history_no=v_history_no and line_no=v_line_no);
            if (v_f_sin=1) then
                UPDATE public.rebatesys_mstcontract SET  f_sin=0 WHERE contract_no=v_contract_no and history_no=v_history_no and line_no=v_line_no;
                UPDATE public.rebatesys_mstcontractdetail SET  f_sin=0 WHERE contract_no=v_contract_no and history_no=v_history_no and line_no=v_line_no;
                UPDATE public.rebatesys_mstcontractreward SET  f_sin=0 WHERE contract_no=v_contract_no and history_no=v_history_no and line_no=v_line_no;
                v_history_no=v_history_no+1;
            end if;
        end if;   
    end if;
    v_sql1 :='
            INSERT INTO public.rebatesys_mstcontract(
            head, contract_no, history_no, f_sin, line_no, p_typeid,compare_typeid,val_typeid,goal_val,count_typeid,f_pb_ignore,                                        
            f_del,ins_date,ins_time,ins_user_id,ins_func_id,ins_ope_id,upd_date,upd_time,upd_user_id,upd_func_id,upd_ope_id)
        VALUES (0, '''||v_contract_no||''', '||v_history_no||',1, '||v_line_no||', '||v_p_typeid||', 
            '||v_compare_typeid||', '||v_val_typeid||', '||v_goal_val||','||v_count_typeid||','||v_f_pb_ignore||',
            0, current_date, current_time, '||v_ins_user_id||', 0, 0,
            current_date,current_time,'||v_upd_user_id||',0, 0);
    ';      

    EXECUTE v_sql1;
    RAISE NOTICE 'v_dept_category_jan_array IS : %', v_dept_category_jan_array;
    if v_p_typeid='1' then

        insert into public.rebatesys_mstcontractdetail(head,contract_no,history_no,f_sin,line_no,
        f_del,ins_date,ins_time,ins_user_id,ins_func_id,ins_ope_id,upd_date,upd_time,upd_user_id,upd_func_id,upd_ope_id,
        s_line_no,departmentcd,catagorycd,jan,seriescd,f_exclude)
        select 0,v_contract_no,v_history_no,1,v_line_no,
             0, current_date, current_time, v_ins_user_id, 0, 0, current_date,current_time,v_upd_user_id,0, 0,
         down_s_line_no,deptCD,categoryCD,singleJan,seriesJan,fExclude from json_to_recordset(''''||v_dept_category_jan_array||'''') 
         as x(s_line_no int, deptCD int,categoryCD int,singleJan int,seriesJan int,fExclude int);

    end if;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.proc_rebate2reachrebateinsert(text)
  OWNER TO postgres;

After create this function,I use:

 select * from public.proc_rebate2reachrebateinsert('{
        "contract_no":"1001",
        "line_no":"1",
        "history_no":0,
        "p_typeid":"1",
        "compare_typeid":1,
        "val_typeid":"2",
        "goal_val":783.11,
        "count_typeid":1,
        "f_pb_ignore":0,
        "all_products":0,
        "judge_typeid":"1",
        "ins_user_id":1,
        "upd_user_id":1,

    "reach_condition_array":[
        {
            "up_s_line_no":1,
            "t_from":.1,
            "t_to":30.1,
            "rebate":11
        },
        {
            "up_s_line_no":2,
            "t_from":30.1,
            "t_to":40.6,
            "rebate":22.5
        }

    ],
    "dept_category_jan_array":[
        {
            "down_s_line_no":1,
            "deptCD":30,
            "categoryCD":2,
            "singleJan":1,
            "seriesJan":0,
            "fExclude":0
        },
        {
            "down_s_line_no":2,
            "deptCD":34,
            "categoryCD":1,
            "singleJan":0,
            "seriesJan":0,
            "fExclude":0
        }

    ]
}')

to test this function,but the error message is:

ERROR: function json_to_recordset(text) doesn't exist LINE 6: ...ptCD,categoryCD,singleJan,seriesJan,fExclude from json_to_re...

How to fix it ?

JGH · Accepted Answer · 2018-08-14 11:33:35Z

3

The variable holding your JSON array is already declared as a string, so you don't need to add extra quotes. It is still perceived as text, so you would need to indicate it is a json structure by using ::json

select [...] 
from json_to_recordset(v_dept_category_jan_array::json) 
  as x(s_line_no int, deptCD int,categoryCD int,singleJan int,seriesJan int,fExclude int);

Let's note that you might have to change your json variable names, as they are case sensitive. For example, you have the data "deptCD":30 but you declare the column as deptCD int, so they won't match. You can change your column to keep the casing "deptCD" int or make is lower case in the JSON "deptcd":30. The same would occur if you rename the column: s_line_no will always be empty since there is no matching entry in the JSON - it is down_s_line_no.

edited Aug 14, 2018 at 11:33

answered Aug 13, 2018 at 16:08

JGH

18.2k5 gold badges41 silver badges58 bronze badges

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

1 Comment

user9912506 Over a year ago

When I use RAISE NOTICE 'v_dept_category_jan_array IS : %', v_dept_category_jan_array; The output is a array like this:v_dept_category_jan_array IS : [ { "down_s_line_no":1, "deptCD":30, "categoryCD":2, "singleJan":1, "seriesJan":0, "fExclude":0 }, { "down_s_line_no":2, "deptCD":34, "categoryCD":1, "singleJan":0, "seriesJan":0, "fExclude":0 }] So it is an array,isn't is?

Collectives™ on Stack Overflow

Postgresql error: function "json_to_recordset(text) doesn't exist"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related