Re: Function error

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 <adrian(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com
>