Re: Plpgsql search_path issue going from 9.3 to 9.6

On 11/13/18 6:27 AM, George Woodring wrote:
> 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.

That is because you have a table tickets in the public schema and a view
tickets in the iss-hackers schema.

Is that what you really want?

>
> \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 <http://www.iglass.net>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com