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