Обсуждение: Function error

Поиск

Список

Период

Сортировка

Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 10:24:29

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;

-- Exposure for single supplier without category filtering

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,

Re: Function error

От

Pavel Stehule

Дата:

08 января 2016 г., 10:36:19

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

Re: Function error

От

"Charles Clavadetscher"

Дата:

08 января 2016 г., 10:43:18

Hello

If I understand you correctly you have two functions create_catexp_ss_1 and create_catexp_ss_2 that you then call from
create_catexp_master.
If so then you probably need to change the call to them:

>                 -- Exposure for single supplier without category filtering
>                 create_catexp_ss_1;
>
>                 -- Exposure for single supplier with category filtering
>                 create_catexp_ss_2;

Should be:

PERFORM create_catexp_ss_1();
PERFORM create_catexp_ss_2();

If necessary with according parameters and assuming that you have no values returned that you need.

Bye
Charles

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sachin Srivastava
> Sent: Freitag, 8. Januar 2016 08:24
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Function error
>
> 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;
>
>
>
>                 -- Exposure for single supplier without category filtering
>
>                 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
>

Re: Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 10:45:30

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

Re: Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 10:48:54

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

Re: Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 10:59:46

Hi,

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

Regards,

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

Re: Function error

От

Pavel Stehule

Дата:

08 января 2016 г., 11:05:27

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

Re: Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 12:08:26

Hi,

I am also getting "syntax error for below function (just some lines of function where I am getting syntax error), please suggest why?

--------------------

-- Image path
        AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.company_id(+)=pcat_catalog_item.company_id
        AND pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
        AND pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
        AND pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
        AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

--------------------------

ERROR: syntax error at or near ")"
LINE 216: AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
^
********** Error **********

ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards

Re: Function error

От

Pavel Stehule

Дата:

08 января 2016 г., 12:24:49

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Hi,

I am also getting "syntax error for below function (just some lines of function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

--------------------

-- Image path
        AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.company_id(+)=pcat_catalog_item.company_id
        AND pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
        AND pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
        AND pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
        AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

--------------------------

ERROR: syntax error at or near ")"
LINE 216:         AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
                                         ^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

Re: Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 12:52:43

Because I have migrated the database from Oracle to Postgres through ORA2PG.

So how I will change it, please suggest.

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

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:
Hi,

I am also getting "syntax error for below function (just some lines of function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

--------------------

-- Image path
        AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.company_id(+)=pcat_catalog_item.company_id
        AND pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
        AND pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
        AND pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
        AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

--------------------------

ERROR: syntax error at or near ")"
LINE 216:         AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
                                         ^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

Re: Function error

От

Pavel Stehule

Дата:

08 января 2016 г., 12:59:38

2016-01-08 10:52 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:

Because I have migrated the database from Oracle to Postgres through ORA2PG.

So how I will change it, please suggest.

http://stackoverflow.com/questions/2269156/converting-from-oracle-join-to-postgres-join

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

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:
Hi,

I am also getting "syntax error for below function (just some lines of function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

--------------------

-- Image path
        AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.company_id(+)=pcat_catalog_item.company_id
        AND pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
        AND pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
        AND pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
        AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

--------------------------

ERROR: syntax error at or near ")"
LINE 216:         AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
                                         ^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

Re: Function error

От

Sachin Srivastava

Дата:

08 января 2016 г., 13:42:51

Thanks Pavel for your help !!!

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

2016-01-08 10:52 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:
Because I have migrated the database from Oracle to Postgres through ORA2PG.

So how I will change it, please suggest.

http://stackoverflow.com/questions/2269156/converting-from-oracle-join-to-postgres-join

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

2016-01-08 10:08 GMT+01:00 Sachin Srivastava <ssr.teleatlas@gmail.com>:
Hi,

I am also getting "syntax error for below function (just some lines of function where I am getting syntax error), please suggest why?

Using Oracle's outer join syntax, not ANSI SQL syntax

Regards

Pavel

--------------------

-- Image path
        AND mk1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.subscriber_id(+)=pcat_catalog_item.subscriber_id
        AND cs1.company_id(+)=pcat_catalog_item.company_id
        AND pcat_catalog_dup_detail.dest_subscriber_id(+)=pcat_item_categorization.subscriber_id
        AND pcat_catalog_dup_detail.dest_supplier_company_id(+)=pcat_item_categorization.item_company_id
        AND pcat_catalog_dup_detail.dest_catalog_code(+)=pcat_item_categorization.catalog_code
        AND mk2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.subscriber_id(+)=pcat_catalog_dup_detail.subscriber_id
        AND cs2.company_id(+)=pcat_catalog_dup_detail.company_id;

--------------------------

ERROR: syntax error at or near ")"
LINE 216:         AND mk1.subscriber_id(+)=pcat_catalog_item.subscribe...
                                         ^
********** Error **********
ERROR: syntax error at or near ")"
SQL state: 42601
Character: 10018

Regards
SS

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Function error