plpgsql and intarray extension; int[] - int[] operator does not exist ?
I have installed the intarray extension installed in the public schema and am attempting to use this in a plpgsql trigger function 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 in a 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 search path 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 predictable path.
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