Error while trying to insert data into timestamp field using plpgsql

Question

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.

score 3 · Accepted Answer · edited May 23 '17 at 11:43

CURRENT_TIME is a reserved word (and a special function), you cannot use it as variable name. You don't need a variable here to begin with:

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

now() is a STABLE function. It does not change across the same transaction. There is no need to capture the result into a variable.

How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

Error while trying to insert data into timestamp field using plpgsql

1 Answers1