I return a string

if (f.entered > tss2 and f.ended <= tse2 and tss != f.ended and f.ended > tss) then tseb = (f.ended + f.duration * interval '1 second')::timestamp(0); RETURN NEXT ( f.ended , tseb, r.login, r.status, r.reason, r.duration, r.t_dur ); end if;

getting

If you’re not a match, you’ll have the correct type of timestamp. It will not be a match. 2. CONTEXT: PL / pgSQL function get_all_foo2 (timestamp without time zone, time zone) line 47 at RETURN NEXT

at the same time ended - timestamp, duration - integer

I can not understand how to lead to a valid type

composite type

 CREATE TYPE public.composite_type AS ( ended TIMESTAMP(0) WITHOUT TIME ZONE, entered TIMESTAMP(0) WITHOUT TIME ZONE, login VARCHAR(100), status VARCHAR(100), reason VARCHAR(100), duration INTEGER, t_dur TIME(0) WITHOUT TIME ZONE ); tseb = to_timestamp(f.ended + f.duration * interval '1 second', 'YYYY-MM-DD HH24:MI:SS')::timestamp without time zone;

also does not pass

HINT: No function matches. You might need to add explicit type casts. QUERY: SELECT to_timestamp (f.ended + f.duration * interval '1 second', 'YYYY-MM-DD HH24: MI: SS') :: timestamp without time zone CONTEXT: PL / pgSQL function get_all_foo2 (timestamp without time zone , timestamp without time zone) line 45 at assignment

and the value is valid 2016-05-21 12:17:00

1 answer 1

The minimum example requested:

 drop type fnsomefunc_type cascade; create type fnsomefunc_type as ( started TIMESTAMP(6) WITHOUT TIME ZONE, ended TIMESTAMP WITHOUT TIME ZONE ); CREATE OR REPLACE FUNCTION fnsomefunc() RETURNS fnsomefunc_type AS $$ DECLARE res timestamp; BEGIN res = (now()::timestamp + 10 * interval '1 second'); RETURN (res, now()::timestamp); END; $$ LANGUAGE 'plpgsql' IMMUTABLE;

melkij = # select fnsomefunc ();
ERROR: returned type
DETAIL: Returned type timestamp without time zone does not match expected type timestamp (6).
CONTEXT: PL / pgSQL function fnsomefunc () while casting return value to function's return type

Looks like an observed error? It seems to be similar. We use explicit type conversions:

 res = (now()::timestamp + 10 * interval '1 second'); RETURN (res::timestamp(6), now()::timestamp);

melkij = # select fnsomefunc ();
("2016-09-15 12: 18: 46.598622", "2016-09-15 12: 18: 36.598622")

Yes, timestamp and timestamp (6) are treated as different data types.

  • valid tseb = f.ended + f.duration * interval '1 second'; RETURN NEXT (f.ended, tseb :: timestamp (0), r.login, r.status, r.reason, r.duration, r.t_dur); - des1roer