postgres cast to type from other schema extension

342 views Asked by At

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;

0

There are 0 answers