Re: Function error

Hi

2016-01-08 8:59 GMT+01:00 Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com>:

> Hi,
>
> Also there is any command to see the invalid and valid function in
> postgres database.
>

No, Postgres is not a Oracle. All functions in database are valid. But it
means some different than in Oracle. That's "all embedded SQL are
syntactically valid". If you need semantic validation, you should to use
plpgsql_check. https://github.com/okbob/plpgsql_check/ .

Regards

Pavel

> Regards,
> SS
>
> On Fri, Jan 8, 2016 at 1:18 PM, Sachin Srivastava <ssr(dot)teleatlas(at)gmail(dot)com
> > wrote:
>
>> 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
>>>>
>>>
>>>
>>
>