Re: Plpgsql search_path issue going from 9.3 to 9.6

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

út 13. 11. 2018 v 14:18 odesílatel George Woodring <george.woodring@xxxxxxxxxx> napsal:

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.

It looks strange. Maybe you have some garbage in iss-hackers schema created in upgrade time.

Hard to say what is wrong without complete schema.

Regards

Pavel

 Thanks,

George Woodring

iGLASS Networks
www.iglass.net