Thanks Charles !!! On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava <ssr.teleat...@gmail.com> wrote:
> 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 >> > >