Re: Function error - Mailing list pgsql-general

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

Thanks Charles !!!

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

Thanks Pavel !!!

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

Hi

2016-01-08 8:24 GMT+01:00 Sachin Srivastava <ssr.teleatlas@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