The situation:
I have a database with multiple schemas. I've installed the ltree extension in public as such:
CREATE EXTENSION IF NOT EXISTS ltree schema public;
and then created the following function
CREATE or replace FUNCTION public.strings_to_lquery_array(text[]) RETURNS lquery[]
AS $$ select array_agg(('*.' || c::text || '.*')::lquery) from unnest($1) c $$
LANGUAGE sql
immutable;
I then run the following code:
set search_path = 'content_api';
select public.strings_to_lquery_array(ARRAY['CURRICULUM']);
which gives the following error:
ERROR: type "lquery" does not exist
LINE 1: select array_agg(('*.' || c::text || '.*')::lquery) from un...
I had assumed that when the function is in public
, the ::lquery casting would also look in public
? but that doesn't seem to be the case.
how do I make this function work from any schema I'm in?
I found a workaround using ::public.lquery
but that just looks a little ugly. I'm also not fully understanding why postgres is doing what it's doing.
What is a better solution?
PS: assume I can't simply do: set search_path = content_api,public;