Running a function in postgresql Throwing a runtime error

Question

I am getting a mysterious error when I try to run a function which returns table my function code is like

CREATE OR REPLACE FUNCTION FN_JOURNEY_SUMMARY(IN ENTITY INTEGER,
                                              IN VEHICLE VARCHAR2,
                                              IN SDATE VARCHAR2,
                                              IN EDATE VARCHAR2,
                                              IN FLG VARCHAR,
                                              IN P_IS_DEBUG CHAR DEFAULT 'Y')
RETURNS TABLE(TRNNAME         VARCHAR2(100),
              SASSETID        VARCHAR2(50),
              DTDATE          VARCHAR2(50),
              IDAYS           NUMBER,
              SMOVINGTIME     VARCHAR2(30),
              SSTOPTIME       VARCHAR2(30),
              SDISTANCE       VARCHAR2(60),
              SCUMMDISTANCE   VARCHAR2(60),
              SAVARAGE        VARCHAR2(60),
              ICNTR           NUMBER)
LANGUAGE PLPGSQL 
AS $FUNCTION$
DECLARE

    TM_START            DATE;
    TM_END              DATE;   
    I_ELAPS_TIME        NUMBER;
BEGIN
TM_START:= CLOCK_TIMESTAMP();
    BEGIN
        CREATE TEMPORARY TABLE GTT_V4JOURNEY_SUMM1 (TRN_NAME        VARCHAR2(100),
                                  S_ASSET_ID        VARCHAR2(50),
                                  DT_DATE           VARCHAR2(50),
                                  I_DAYS            NUMBER,
                                  S_MOVING_TIME     VARCHAR2(30),
                                  S_STOP_TIME       VARCHAR2(30),
                                  S_DISTANCE        VARCHAR2(60),
                                  S_CUMM_DISTANCE   VARCHAR2(60),
                                  S_AVARAGE         VARCHAR2(60),
                                  i_cntr            number);
    EXCEPTION
        WHEN OTHERS THEN
        DROP TABLE GTT_V4JOURNEY_SUMM1;
        CREATE TEMPORARY TABLE GTT_V4JOURNEY_SUMM1 (TRN_NAME        VARCHAR2(100),
                                  S_ASSET_ID        VARCHAR2(50),
                                  DT_DATE           VARCHAR2(50),
                                  I_DAYS            NUMBER,
                                  S_MOVING_TIME     VARCHAR2(30),
                                  S_STOP_TIME       VARCHAR2(30),
                                  S_DISTANCE        VARCHAR2(60),
                                  S_CUMM_DISTANCE   VARCHAR2(60),
                                  S_AVARAGE         VARCHAR2(60),
                                  i_cntr            number);

    END;

        INSERT INTO GTT_V4JOURNEY_SUMM1
        SELECT * FROM FN_JOURNEY_SUMM_WEEK(ENTITY,VEHICLE, SDATE,EDATE,P_IS_DEBUG);

IF P_IS_DEBUG = 'Y' THEN
TM_END:=CLOCK_TIMESTAMP();
RAISE NOTICE 'THE START TIME WAS %',TM_START;
RAISE NOTICE 'THE END TIME WAS %',TM_END;
I_ELAPS_TIME:=EXTRACT(MICROSECONDS FROM (TM_END - TM_START))/1000;
RAISE NOTICE 'THE TIME TAKEN IS >>>>>>-------------->>>>>>> %',I_ELAPS_TIME;
END IF;
    RAISE NOTICE '*** end of all ***';
    RETURN QUERY SELECT * FROM GTT_V4JOURNEY_SUMM1 ;

END $FUNCTION$;

When I run the function with:

select * from FN_JOURNEY_SUMMARY(100,'NL01L0639','28/03/2014','23/06/2014','W',1);

This it is throwing me an error:

invalid input syntax for type timestamp: "28-Mar:28-Mar-14(13)"

which is storing into DTDATE column of the returning table which is a character varying type.

But when I compile the same function with a different name no errors are thrown and it runs smoothly.

Did you try to name the columns on which to insert? It is good practice: INSERT INTO GTT_V4JOURNEY_SUMM1 (col1, col2...) — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 9, 2014 at 8:23
Show the full error message. Also the code for FN_JOURNEY_SUMM_WEEK in which I suspect the error is happening. — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 9, 2014 at 8:30
@ClodoaldoNeto no sir i have check that function it is working fine and no errors are throwing from it — smn_onrocks
– smn_onrocks, Commented Aug 9, 2014 at 8:36
Run \df FN_JOURNEY_SUMMARY to check if there is more than one function with that name. Each will accept a different set of parameters. That is the only explanation I can think of when i am compiling the same function with a different name no errors are throwing — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 9, 2014 at 8:45
Always provide your version of Postgres. Plus, the complete, verbatim error message. Also, what's with the upper-casing? Are you coming from Oracle? — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 10, 2014 at 9:42

Erwin Brandstetter · Accepted Answer · 2014-08-10 09:48:35Z

1

This is a mess. And it certainly does not compile, like you claim.

VARCHAR2 and NUMBER are not valid data types in Postgres.

TM_START and TM_END should be timestamp or timestamptz, not date.

P_IS_DEBUG should be boolean.

Your INSERT statement should have a target list.

answered Aug 10, 2014 at 9:48

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running a function in postgresql Throwing a runtime error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related