Re: Function error - Mailing list pgsql-general

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@aklaver.com