Plpgsql search_path issue going from 9.3 to 9.6

From: George Woodring <george(dot)woodring(at)iglass(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Plpgsql search_path issue going from 9.3 to 9.6
Date: 2018-11-13 13:17:47
Message-ID: CACi+J=TiP-mLHTe9EY=pi8Y1JwnhggKLGWbaRU19txdd1tVCow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
iGLASS Networks
www.iglass.net

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2018-11-13 13:43:26 Re: Plpgsql search_path issue going from 9.3 to 9.6
Previous Message talk to ben 2018-11-13 10:50:09 Re: pg9.6 when is a promoted cluster ready to accept "rewind" request?