Re: Function error

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