Thanks Pavel !!!
On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:
> Hi
>
>
>
> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleat...@gmail.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
>
