Re: [GENERAL] Function error

[Albe Laurenz]

Sachin Srivastava wrote:
> In my function the problem is that global variables defined inside the 
> function.
> These variables are visible to functions defined inside a function.
> If we move these inner functions to outside of the main function,
> they will lose the visibility of the global variables.
> So I think that's why we have to correct this functions rather then creating 
> it as individual.

I see.
You'll have to rewrite the functions then, probably by passing the shared
values as parameters instead of having them in global variables.

> We have migrated our data from Oracle to Postgres through ORA2PG Tool
> after that we are getting this error for this function.

ora2pg cannot translate all PL/SQL code, you have to be prepared to rewrite
most nontrivial functions.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function error

[Albe Laurenz]

Sachin Srivastava wrote:
> I am getting the below error for function, please see the bold line in 
> "Function code", please suggest
> what I will do to correct this code.
> 
>  ---
> 
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
> ^
> CONTEXT:  invalid type name "* from logError(msg text) is

That error message does not look like it could come from the
function definition below, but the function definition is clearly
syntactically incorrect:

> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
> subscriberid bigint,
> incompanyid bigint,
> loginname text)
>   RETURNS void AS
> $BODY$
> DECLARE

[...]

>   SELECT logError(v_errorMesg text, procedureName text, subscriberID bigint, 
> companyID bigint,
> supplierSku text, loginName text) is
> 
> BEGIN
>  insert into SMERROR_LOG
>  (error_message, method_name, system_message, error_log_id, 
> subscriber_id, company_id,
> creation_date, creation_user, update_date, update_user)
>  values(v_errorMesg, procedureName, supplierSku, 
> nextval('smerror_log_sequence'), subscriberID,
> companyID, LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>   end;

You cannot declare a function inside the DECLARE section of
another function.  Besides, what is "SELECT logError" supposed to mean?

You will have to declare each of the helper functions with ist own
CREATE FUNCTION statement.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Function error

[Sachin Srivastava]

Dear Team,

I am getting the below error for function, please see the bold line in
"Function code", please suggest what I will do to correct this code.

 ---

ERROR:  syntax error at or near "*"
LINE 35: SELECT * from logError(msg text) is
^
CONTEXT:  invalid type name "* from logError(msg text) is

BEGIN
insert into SMERROR_LOG
(error_message,  error_log_id, method_name, subscriber_id, company_id,
creation_date, creation_user, update_date, update_user)
   values(msg,  nextval('smerror_log_sequence'),
'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP,
'Admin', LOCALTIMESTAMP, 'Admin')"
** Error **

ERROR: syntax error at or near "*"
SQL state: 42601
Character: 1276
Context: invalid type name "* from logError(msg text) is

BEGIN
insert into SMERROR_LOG
(error_message,  error_log_id, method_name, subscriber_id, company_id,
creation_date, creation_user, update_date, update_user)
   values(msg,  nextval('smerror_log_sequence'),
'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP,
'Admin', LOCALTIMESTAMP, 'Admin')"

--


*Function Code as below:*


--


-- Function: cp_property_room_count_trans(bigint, bigint, text)

-- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);

CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
subscriberid bigint,
incompanyid bigint,
loginname text)
  RETURNS void AS
$BODY$
DECLARE


  CRS_1 CURSOR FOR SELECT distinct company_id from CP_ROOM_COUNT_STAGING
where subscriber_id=subscriberID and (process_flag is null or
process_flag=0)  order by company_id;

  --Version:
  -- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
  --   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
  -- 06/08/07 (Bea) insert value for CP_ROOM_TYPE_COUNT.room_budget_home_amt
  --  If phaseID is new, validate that these as required fields
:CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
  --   Will set process_flag=2 if fail the validation.
  --  CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated per
business rule 15327
  --

  RW_1 RECORD; --CRS_1 %ROWTYPE;
  err_msg_1 varchar(100);
  v_errorMessage_1 varchar(4000);
  sucessfulRecCount bigint :=0;
  failedRecCount bigint :=0;








*  --  SELECT
logError(v_errorMesg text, procedureName text, subscriberID bigint,
companyID bigint, supplierSku text, loginName text) is  BEGIN insert
into SMERROR_LOG (error_message, method_name, system_message,
error_log_id, subscriber_id, company_id, creation_date, creation_user,
update_date, update_user)  values(v_errorMesg, procedureName,
supplierSku, nextval('smerror_log_sequence'), subscriberID, companyID,
LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');end;*

*  --*

  FUNCTION Invoke_InsertPropertyRCProcess(subID in number, compID in
number, loginName in varchar2) is

  CRS CURSOR FOR
 SELECT st.*, st.rowid, ph.project_code from
CP_ROOM_COUNT_STAGING st, CP_PROJECT_PHASE ph
 where st.SUBSCRIBER_ID =subID and st.company_id=compID and
(st.process_flag is null or st.process_flag=0)
 and ph.SUBSCRIBER_ID(+)= st.SUBSCRIBER_ID AND ph.PHASE_ID(+)=
st.CORP_PHASE_ID order by st.subscriber_id, st.company_id, st.phase_id;

 RW CRS%ROWTYPE;
 err_msg varchar2(100);
 v_errorMessage varchar2(4000);
 v_errorCountExp number :=0;
 MAX_ERR_WRITTEN constant number :=10;
 recCount number := 0;

 checkCount number;
 startRowCounter number :=0;
 isValidated boolean :=true;


 begin
   logError('Begin Invoke_InsertPropertyRCProcess subID:'|| subID || '
and compID:'||compID,'Invoke_InsertPropertyRCProcess', subID, compID,null,
loginName);

   -- the row_number counter starting number
   select max(Row_number) into startRowCounter from CP_ROOM_TYPE_COUNT
   where subscriber_id=subID and company_id=compID;

   if(startRowCounter is null) then
   startRowCounter :=0;
   end if;

   open CRS;
   loop
  begin -- the begin inside the loop
 fetch CRS into RW;
 IF NOT FOUND THEN EXIT; END IF; -- apply on CRS
   recCount := recCount+1;
   startRowCounter := startRowCounter+1;

   --check to if phase_id already existed in CP_PROPERTY_PHASE
if not insert
   select  count(1)  into checkCount from CP_PROPERTY_PHASE
   where subscriber_id=subID and company_id=compID and
PHASE_ID=RW.PHASE_ID;

   isValidated := true;
--check to see if pass validation.
   if(checkCount =0 and (RW.phase_start_date is null or

Re: [GENERAL] Function error

[Adrian Klaver]

On 01/13/2016 01:18 AM, Sachin Srivastava wrote:
> 
> Dear Team,
> 
> I am getting the below error for function, please see the bold line in 
> "Function code", please suggest what I will do to correct this code.
> 
>   ---
> 
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
>  ^
> CONTEXT:  invalid type name "* from logError(msg text) is
> 

> 
> *_Function Code as below:_*
> 
> 
> --
> 
> 
> -- Function: cp_property_room_count_trans(bigint, bigint, text)
> 
> -- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
> 
> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
>  subscriberid bigint,
>  incompanyid bigint,
>  loginname text)
>RETURNS void AS
> $BODY$
> DECLARE
> 
> 
>CRS_1 CURSOR FOR SELECT distinct company_id from 
> CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag 
> is null or process_flag=0)  order by company_id;
> 
>--Version:
>-- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
>--   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
>-- 06/08/07 (Bea) insert value for 
> CP_ROOM_TYPE_COUNT.room_budget_home_amt
>--  If phaseID is new, validate that these as required 
> fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
>--   Will set process_flag=2 if fail the validation.
>--  CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated 
> per business rule 15327
>--
> 
>RW_1 RECORD; --CRS_1 %ROWTYPE;
>err_msg_1 varchar(100);
>v_errorMessage_1 varchar(4000);
>sucessfulRecCount bigint :=0;
>failedRecCount bigint :=0;
> 
> *  --
>SELECT logError(v_errorMesg text, procedureName text, subscriberID 
> bigint, companyID bigint, supplierSku text, loginName text) is

I have not even tried to go through all of this function, but the above is a 
problem in and of itself.

If you want to do something that returns no result then you need to use PERFORM:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

That stills the question of what ..'loginName text) is' is supposed to be doing?

If you are trying to SELECT the result of logError into a  variable then you 
need to use INTO:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

If you are trying to do something else, then as Albe said, you need to let us 
know what that is?


> 
> BEGIN
>   insert into SMERROR_LOG
>   (error_message, method_name, system_message, error_log_id, 
> subscriber_id, company_id, creation_date, creation_user, update_date, 
> update_user)
>   values(v_errorMesg, procedureName, supplierSku, 
> nextval('smerror_log_sequence'), subscriberID, companyID, 
> LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>end;*



-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function error

[David G. Johnston]

On Wed, Jan 13, 2016 at 10:06 PM, Sachin Srivastava  wrote:

> Dear Albe,
>
> Thanks for your support !!!
>
> In my function the problem is that global variables defined inside the
> function. These variables are visible to functions defined inside a
> function.
>

​Holy Crap...PostgreSQL functions support closures and global variables?
Who knew?  Well, aside from that fact that relations are global, anyway...

The problem you seem to have is trying to do something in PostgreSQL
exactly like you'd do it in Oracle.  While I'm sure many things can be
ported over you seem to have stumbled upon one of those things that likely
will need to be written using PostgreSQL idioms.

​David J.​

Re: [GENERAL] Function error

[Sachin Srivastava]

Dear Albe,

Thanks for your support !!!

In my function the problem is that global variables defined inside the
function. These variables are visible to functions defined inside a
function. If we move these inner functions to outside of the main function,
they will lose the visibility of the global variables. So I think that's
why we have to correct this functions rather then creating it as
individual.

We have migrated our data from Oracle to Postgres through ORA2PG Tool after
that we are getting this error for this function.

Dear Adrian,

I have also used the PERFORM option but still it's not running.

Regards,
Sachin



On Thu, Jan 14, 2016 at 12:37 AM, Adrian Klaver 
wrote:

> On 01/13/2016 01:18 AM, Sachin Srivastava wrote:
> >
> > Dear Team,
> >
> > I am getting the below error for function, please see the bold line in
> > "Function code", please suggest what I will do to correct this code.
> >
> >   ---
> >
> > ERROR:  syntax error at or near "*"
> > LINE 35: SELECT * from logError(msg text) is
> >  ^
> > CONTEXT:  invalid type name "* from logError(msg text) is
> >
>
> >
> > *_Function Code as below:_*
> >
> >
> >
> --
> >
> >
> > -- Function: cp_property_room_count_trans(bigint, bigint, text)
> >
> > -- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
> >
> > CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
> >  subscriberid bigint,
> >  incompanyid bigint,
> >  loginname text)
> >RETURNS void AS
> > $BODY$
> > DECLARE
> >
> >
> >CRS_1 CURSOR FOR SELECT distinct company_id from
> > CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag
> > is null or process_flag=0)  order by company_id;
> >
> >--Version:
> >-- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
> >--   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
> >-- 06/08/07 (Bea) insert value for
> > CP_ROOM_TYPE_COUNT.room_budget_home_amt
> >--  If phaseID is new, validate that these as required
> > fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
> >--   Will set process_flag=2 if fail the validation.
> >--  CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated
> > per business rule 15327
> >--
> >
> >RW_1 RECORD; --CRS_1 %ROWTYPE;
> >err_msg_1 varchar(100);
> >v_errorMessage_1 varchar(4000);
> >sucessfulRecCount bigint :=0;
> >failedRecCount bigint :=0;
> >
> > *  --
> >SELECT logError(v_errorMesg text, procedureName text, subscriberID
> > bigint, companyID bigint, supplierSku text, loginName text) is
>
> I have not even tried to go through all of this function, but the above is
> a problem in and of itself.
>
> If you want to do something that returns no result then you need to use
> PERFORM:
>
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
>
> That stills the question of what ..'loginName text) is' is supposed to be
> doing?
>
> If you are trying to SELECT the result of logError into a  variable then
> you need to use INTO:
>
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
> If you are trying to do something else, then as Albe said, you need to let
> us know what that is?
>
>
> >
> > BEGIN
> >   insert into SMERROR_LOG
> >   (error_message, method_name, system_message, error_log_id,
> > subscriber_id, company_id, creation_date, creation_user, update_date,
> > update_user)
> >   values(v_errorMesg, procedureName, supplierSku,
> > nextval('smerror_log_sequence'), subscriberID, companyID,
> > LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
> >end;*
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Function error

[Pavel Stehule]

Hi

2016-01-08 8:59 GMT+01:00 Sachin Srivastava :

> 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  > 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 
>>> wrote:
>>>
 Hi



 2016-01-08 8:24 GMT+01:00 Sachin Srivastava :

> 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 

Re: [GENERAL] Function error

[Sachin Srivastava]

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
SS

Re: [GENERAL] Function error

[Pavel Stehule]

2016-01-08 10:08 GMT+01:00 Sachin Srivastava :

> 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: [GENERAL] Function error

[Sachin Srivastava]

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 
wrote:

> Thanks Charles !!!
>
> On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava  > wrote:
>
>> Thanks Pavel !!!
>>
>> On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule 
>> wrote:
>>
>>> Hi
>>>
>>>
>>>
>>> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava :
>>>
 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: [GENERAL] Function error

[Sachin Srivastava]

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 
wrote:

>
>
> 2016-01-08 10:08 GMT+01:00 Sachin Srivastava :
>
>> 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: [GENERAL] Function error

[Pavel Stehule]

2016-01-08 10:52 GMT+01:00 Sachin Srivastava :

> 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 
> wrote:
>
>>
>>
>> 2016-01-08 10:08 GMT+01:00 Sachin Srivastava :
>>
>>> 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: [GENERAL] Function error

[Sachin Srivastava]

Thanks Pavel for your help !!!

On Fri, Jan 8, 2016 at 3:28 PM, Pavel Stehule 
wrote:

>
>
> 2016-01-08 10:52 GMT+01:00 Sachin Srivastava :
>
>> 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 
>> wrote:
>>
>>>
>>>
>>> 2016-01-08 10:08 GMT+01:00 Sachin Srivastava :
>>>
 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: [GENERAL] Function error

[Charles Clavadetscher]

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-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@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
> 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Function error

[Sachin Srivastava]

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule 
wrote:

> Hi
>
>
>
> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava :
>
>> 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: [GENERAL] Function error

[Sachin Srivastava]

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava 
wrote:

> Thanks Pavel !!!
>
> On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>>
>>
>> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava :
>>
>>> 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
>>
>
>

[GENERAL] Function error

[Sachin Srivastava]

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: [GENERAL] Function error

[Pavel Stehule]

Hi



2016-01-08 8:24 GMT+01:00 Sachin Srivastava :

> 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