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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
