Hi,
Also there is any command to see the invalid and valid function in postgres
database.
Regards,
SS
On Fri, Jan 8, 2016 at 1:18 PM, Sachin Srivastava <ssr.teleat...@gmail.com>
wrote:
> 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
>>>
>>
>>
>
