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.
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$;