Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Unanswered: timezone function dose not work in PL/pgSQL

    Hi,
    I have a problem when using timezone function in PL/pgSQL Stored Procedure. Can anyone help?
    I have 2 test functions as following:

    CREATE OR REPLACE FUNCTION TEST_TIMEZONE_1() RETURNS timestamp AS '
    DECLARE
    dtzRetDate timestamp;
    BEGIN
    dtzRetDate := timezone(''UTC'', ''2005-01-01 00:00:00 AEST'');
    RETURN dtzRetDate;
    END;
    ' LANGUAGE plpgsql;


    CREATE OR REPLACE FUNCTION TEST_TIMEZONE_2() RETURNS timestamp AS '
    DECLARE
    dtzRetDate timestamp;
    strTempDateTime varchar(200);
    BEGIN
    strTempDateTime := ''2005-01-01 00:00:00 AEST'';
    dtzRetDate := timezone(''UTC'', strTempDateTime);
    RETURN dtzRetDate;
    END;
    ' LANGUAGE plpgsql;


    SELECT TEST_TIMEZONE_1();
    Will return result:
    2004-12-31 14:00:00

    SELECT TEST_TIMEZONE_2();
    Will return resutl:
    ERROR: function timezone("unknown", character varying) does not exist
    HINT: No function matches the given name and argument types. You may need to add explicit type casts.
    CONTEXT: SQL statement "SELECT timezone('UTC', $1 )"
    PL/pgSQL function "test_timezone_2" line 6 at assignment


    My question is:
    Why timezone() function only accepts the string but not for the variable?

    It would be very appricate If anyone can help me to get around with this problem.

    Thanks

    Tom

  2. #2
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    String "ERROR: function timezone("unknown", character varying) does not exist" say that Postgres don't recognize type of first argument.
    It even said to you that you must to do - "You may need to add explicit type casts"
    Try timezone(''UTC''::varchar, strTempDateTime).

  3. #3
    Join Date
    Jan 2005
    Posts
    3
    Hi,
    Thanh for replying. I got it work now. However I have another problem with timezone. Would be very appreciate if someone could help. Thanks

    I am writing the routine to convert the Local Time to UTC and UTC back to Local Time. One of the problem I have is: I can not set the timezone in side the Stored Procedure.
    Example:


    CREATE OR REPLACE FUNCTION SET_TIME_ZONE_1() RETURNS varchar AS '
    DECLARE
    BEGIN
    SET TIME ZONE ''NZ'';
    return NULL;
    END;
    ' LANGUAGE plpgsql;

    CREATE OR REPLACE FUNCTION SET_TIME_ZONE_2() RETURNS varchar AS '
    DECLARE
    myTZ varchar(40);
    BEGIN
    myTZ := ''NZ'';
    SET TIME ZONE myTZ;
    return NULL;
    END;
    ' LANGUAGE plpgsql;

    Function SET_TIME_ZONE_1() has no problem but function SET_TIME_ZONE_2() returns following error:

    ERROR: syntax error at or near "$1" at character 16
    QUERY: SET TIME ZONE $1
    CONTEXT: PL/pgSQL function "set_time_zone_2" line 5 at SQL statement

  4. #4
    Join Date
    Jan 2005
    Posts
    3
    Don't worry.
    I got it with:

    myTZ := ''NZ'';
    strSetTimeZone := ''SET TIME ZONE ''''''||myTz||'''''''';
    EXECUTE(strSetTimeZone);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •