We are having an issue with one of our plpgsql functions after migrating from 9.3 to 9.6. The function works fine until you change the search path.
psql (9.6.10)
Type "help" for help.
woody=> select ticket_summary(8154);
ticket_summary
-------------------------------------------------------------------
{58451,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
(1 row)
woody=> set search_path to "iss-hackers", public;
SET
woody=> select ticket_summary(8154);
ERROR: invalid input syntax for type timestamp with time zone: "woodring"
CONTEXT: PL/pgSQL function ticket_summary(integer) line 11 at SQL statement
It is confused which column is which after the change.
The tables used by the function are:
public.tickets - A table with 3 child tables
iss-hackers.tickets - A view of public.tickets with a where clause.
public.followups - A table with 3 child tables.
CREATE OR REPLACE FUNCTION ticket_summary(int4) RETURNS int8[] AS $$
DECLARE
tid ALIAS FOR $1;
cstate public.followups.state%TYPE := 1;
ticket public.tickets%ROWTYPE;
followup public.followups%ROWTYPE;
summary int8[] := '{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}';
lastdate public.followups.date%TYPE;
BEGIN
SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;
IF NOT FOUND THEN
return summary;
END IF;
lastdate := ticket.opendate;
FOR followup IN SELECT * FROM public.followups WHERE ticketsid=tid AND state IS NOT NULL ORDER BY date LOOP
summary[cstate] := summary[cstate] + extract( EPOCH FROM (followup.date - lastdate))::int;
cstate := followup.state;
lastdate := followup.date;
END LOOP;
summary[cstate] := summary[cstate] + extract( EPOCH FROM (current_timestamp - lastdate))::int;
RETURN summary;
END;
$$ LANGUAGE plpgsql;
I assume I can fix this by putting the function into each of the schemas, but I thought I would ask opinions before doing so.
Thanks,
George Woodring