I think the issue is that the function is not putting the data into the tickets%ROWTYPE correctly. When I do \d on public.tickets and iss-hackers.tickets, the columns are in a different order.
\d public.tickets Column | Type | Modifiers --------------+--------------------------+------------------------------------------------------------- ticketsid | integer | not null default nextval('tickets_ticketsid_seq'::regclass) opendate | timestamp with time zone | default now() state | smallint | default 1 opentech | character varying(50) | priority | smallint | default 10 severity | smallint | default 30 problem | character varying(300) | summary | text | parent | integer | remed | boolean | default false remed2 | boolean | default false remed_hstart | timestamp with time zone | autoclean | boolean | default false remoteid | character varying | remotesync | timestamp with time zone | sla_time | interval | sla_alarm | boolean | \d iss-hackers.tickets View "iss-hackers.tickets" Column | Type | Modifiers --------------+--------------------------+----------- ticketsid | integer | opentech | character varying(50) | summary | text | parent | integer | opendate | timestamp with time zone | priority | smallint | problem | character varying(300) | autoclean | boolean | state | smallint | severity | smallint | remed | boolean | remed2 | boolean | remoteid | character varying | remotesync | timestamp with time zone | sla_time | interval | sla_alarm | boolean | remed_hstart | timestamp with time zone | tableoid | oid | The error message is saying column2 is not a timestamp, which the public table is a timestamp for column2. If I change my SELECT in the function from SELECT * to SELECT opendate I can fix my issue easily. George iGLASS Networks www.iglass.net On Tue, Nov 13, 2018 at 8:44 AM Pavel Stehule <pavel.steh...@gmail.com> wrote: > Hi > > Ășt 13. 11. 2018 v 14:18 odesĂlatel George Woodring < > george.woodr...@iglass.net> 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 >> >