Re: Function error

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com>
wrote:

> Thanks Pavel !!!
>
> On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>>
>>
>> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com>:
>>
>>> Dear Concern,
>>>
>>>
>>>
>>> I am creating below function *“create_catexp_ss_master()” *and getting
>>> error as below, I have already created dependent function firstly
>>> successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but
>>> still getting error, please suggest why?
>>>
>>>
>>>
>>> ERROR: syntax error at or near "create_catexp_ss_1"
>>>
>>> LINE 38: create_catexp_ss_1;
>>>
>>> ^
>>>
>>> ********** Error **********
>>>
>>>
>>>
>>> ERROR: syntax error at or near "create_catexp_ss_1"
>>>
>>> SQL state: 42601
>>>
>>> Character: 1104
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> -- Function: create_catexp_ss_master()
>>>
>>>
>>>
>>> -- DROP FUNCTION create_catexp_ss_master();
>>>
>>>
>>>
>>> CREATE OR REPLACE FUNCTION create_catexp_ss_master()
>>>
>>> RETURNS void AS
>>>
>>> $BODY$
>>>
>>> DECLARE
>>>
>>>
>>>
>>> -- Build snapshot tables for catalog itme exposure.
>>>
>>>
>>>
>>> -- Versions:
>>>
>>> -- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13
>>>
>>> -- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
>>> and from the Code. 05/23/13
>>>
>>> --
>>>
>>>
>>>
>>> v_count_before bigint;
>>>
>>> v_count_after bigint;
>>>
>>> v_start_time timestamp;
>>>
>>> v_err_msg varchar(1000);
>>>
>>> v_set_name varchar(10);
>>>
>>>
>>>
>>>
>>> BEGIN
>>>
>>> v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;
>>>
>>> SELECT LOCALTIMESTAMP INTO v_start_time ;
>>>
>>>
>>>
>>> if v_set_name='A' then
>>>
>>> SELECT count(1) INTO v_count_before
>>> FROM pcat_exp_supp_buyer_ss_a;
>>>
>>> else
>>>
>>> SELECT count(1) INTO v_count_before
>>> FROM pcat_exp_supp_buyer_ss_b;
>>>
>>> end if;
>>>
>>>
>>>
>>> -- Remove old data.
>>>
>>> EXECUTE 'truncate table
>>> pcat_exp_supp_buyer_ss_'||v_set_name;
>>>
>>> EXECUTE 'truncate table
>>> pcat_exp_supp_cat_buyer_ss_'||v_set_name;
>>>
>>
>> Attention - this is potentially serious security bug
>>
>> EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
>> v_set_name);
>>
>>
>>>
>>>
>>> -- Exposure for single supplier without category
>>> filtering
>>>
>>> create_catexp_ss_1;
>>>
>>
>> you have to call this function via PERFORM statement
>>
>> PERFORM create_catexp_ss_1();
>>
>>
>>>
>>>
>>> -- Exposure for single supplier with category filtering
>>>
>>>
>>> create_catexp_ss_2;
>>>
>>>
>>>
>>> if v_set_name='A' then
>>>
>>> SELECT count(1) INTO v_count_after FROM
>>> pcat_exp_supp_buyer_ss_a;
>>>
>>> else
>>>
>>> SELECT count(1) INTO v_count_after FROM
>>> pcat_exp_supp_buyer_ss_b;
>>>
>>> end if;
>>>
>>>
>>>
>>> -- Log
>>>
>>> create_ss_log('Catalog Exposure', v_start_time,
>>> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>>>
>>> v_count_before, v_count_after, null);
>>>
>>>
>>>
>>> exception -- log error
>>>
>>> when others then
>>>
>>> v_err_msg := SQLERRM;
>>>
>>> create_ss_log('Catalog Exposure - Error', v_start_time,
>>> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>>>
>>> v_count_before,
>>> v_count_after, v_err_msg);
>>>
>>>
>>>
>>> END;
>>>
>>> $BODY$
>>>
>>> LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>>>
>>> COST 100;
>>>
>>> ALTER FUNCTION create_catexp_ss_master()
>>>
>>> OWNER TO postgres;
>>>
>>>
>>>
>>>
>>>
>>>
>>> Regards,
>>> SS
>>>
>>>
>> Regards
>>
>> Pavel Stehule
>>
>
>