Обсуждение: plpgsql and intarray extension; int[] - int[] operator does notexist ?

I have installed the intarray extension installed in the public schema and am attempting to  use this in a plpgsql
triggerfunction from another schema. 
When the triggers executes this I get an exception to the effect

{
    "hint": "No operator matches the given name and argument type(s). You might need to add explicit type casts.",
    "details": null,
    "code": "42883",
    "message": "operator does not exist: integer[] - integer[]"
}

However,  If I write a similar test function and attempt similar array arithmetic successfully from a different schema
ina non-trigger function It recognizes the intarray methods. 

CREATE OR REPLACE FUNCTION admin.djd_test()
  RETURNS integer[] AS
$BODY$
DECLARE
    _old_tag_ids INTEGER[];
    _new_tag_ids INTEGER[];


BEGIN
    _old_tag_ids := ARRAY[1,2,3];
    _new_tag_ids := ARRAY[3,4,5];

    RETURN _old_tag_ids - _new_tag_ids;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE


Of course in the trigger function  the declared int[] arrays  the content is dynamically initialized.
Any suggestions as to why the int[] operations are not understood in the trigger context.?

.
Thanks


Dave Day

"Day, David" <david.day@redcom.com> writes:
> Any suggestions as to why the int[] operations are not understood in the trigger context.?

The search_path in the trigger probably doesn't include public.
You could add a "SET search_path = whatever" clause to the trigger
function definition to ensure it runs with a predictable path.

            regards, tom lane

Tom

I was thinking something similar after finding that my test function recreated in the problematic schema would execute
correctly
As one user-role but not another and that they had different search_path settings.

After adding public to search patch for that role all was good.

The error message "no operator matches the given name and argument type....." does not make me easily come around to a
searchpath issue. 
In any event thanks much for the assistance.


Issue resolved.


Dave




-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, November 19, 2018 12:56 PM
To: Day, David <david.day@redcom.com>
Cc: pgsql-general@postgresql.org
Subject: Re: plpgsql and intarray extension; int[] - int[] operator does not exist ?

"Day, David" <david.day@redcom.com> writes:
> Any suggestions as to why the int[] operations are not understood in the trigger context.?

The search_path in the trigger probably doesn't include public.
You could add a "SET search_path = whatever" clause to the trigger function definition to ensure it runs with a
predictablepath. 

            regards, tom lane

On Mon, Nov 19, 2018 at 11:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "Day, David" <david.day@redcom.com> writes:
> > Any suggestions as to why the int[] operations are not understood in the trigger context.?
>
> The search_path in the trigger probably doesn't include public.
> You could add a "SET search_path = whatever" clause to the trigger
> function definition to ensure it runs with a predictable path.

Might be worth considering:
SET LOCAL search_path = whatever

So that LOCAL makes the change local to the transaction.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Mon, Nov 19, 2018 at 11:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The search_path in the trigger probably doesn't include public.
>> You could add a "SET search_path = whatever" clause to the trigger
>> function definition to ensure it runs with a predictable path.

> Might be worth considering:
> SET LOCAL search_path = whatever
> So that LOCAL makes the change local to the transaction.

What I was suggesting was to create the trigger function using

create function ... as $$ ...body... $$ set search_path = whatever;

That makes the setting local to each function call.

            regards, tom lane

On Mon, Nov 19, 2018 at 4:36 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Merlin Moncure <mmoncure@gmail.com> writes:
> > On Mon, Nov 19, 2018 at 11:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> The search_path in the trigger probably doesn't include public.
> >> You could add a "SET search_path = whatever" clause to the trigger
> >> function definition to ensure it runs with a predictable path.
>
> > Might be worth considering:
> > SET LOCAL search_path = whatever
> > So that LOCAL makes the change local to the transaction.
>
> What I was suggesting was to create the trigger function using
>
> create function ... as $$ ...body... $$ set search_path = whatever;
>
> That makes the setting local to each function call.

Oh, I see.  Yes, that's even better.

merlin