Re: Function error

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2016-01-08 8:59 GMT+01:00 Sachin Srivastava <ssr.teleatlas@xxxxxxxxx>:

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.teleatlas@xxxxxxxxx> wrote:

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava <ssr.teleatlas@xxxxxxxxx> wrote:

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

Hi

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@xxxxxxxxx>:

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