Re: Function error

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