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@aklaver.com
