postgresqltimestampplpgsql

Error while trying to insert data into timestamp field using plpgsql


I have the following plpgsql function:

CREATE OR REPLACE FUNCTION test_func(OUT pid bigint)
   RETURNS bigint AS
$BODY$
DECLARE
   current_time timestamp with time zone = now();
BEGIN
   INSERT INTO "TEST"(created)
   VALUES (current_time) RETURNING id INTO pid;
END
$BODY$
  LANGUAGE plpgsql;

select * from test_func();

The above gives an error:

column "created" is of type timestamp with time zone but expression is of type time with time zone

Insertion query without function:

INSERT INTO "TEST"(created)
VALUES (now())
RETURNING id INTO pid;

Or if now() is used directly without defining variable it works.


Solution

  • CURRENT_TIME is a reserved word - a special function to return the current time with time zone (timetz), which is notably different from timestamp with time zone (timestamptz). Also largely useless because the data type timetz is discouraged (but defined by the SQL standard.)
    You cannot use current_time as variable name. Also, you don't need a variable here to begin with:

    CREATE OR REPLACE FUNCTION test_func(OUT pid bigint)
      LANGUAGE plpgsql AS
    $func$
    BEGIN
       INSERT INTO "TEST"(created)
       VALUES (now())
       RETURNING id
       INTO   pid;
    END
    $func$;
    

    now() is a STABLE function, returning the desired timestamptz value - the internal implementation of CURRENT_TIMESTAMP. See:

    It does not change across the same transaction, so there is no need to capture the result into a variable. (But the wrapping function isn't STABLE because of the INSERT.) See:

    Aside: the simple example could just be a plain SQL function:

    CREATE OR REPLACE FUNCTION test_func(OUT pid bigint)
      LANGUAGE sql AS
    $func$
    INSERT INTO "TEST"(created)
    VALUES (now())
    RETURNING id;
    $func$;