On 11/13/18 5:17 AM, George Woodring wrote:
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"
Well the above is the base of the issue, the string 'woodring' is being
used as a timestamp entry. Somewhere wires are getting crossed.
In the function below you have:
SELECT * INTO ticket FROM tickets WHERE ticketsid=tid;
This is the only place where you do not schema qualify a table.
Is there more then tickets table?
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
iGLASS Networks
www.iglass.net <http://www.iglass.net>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
