Re: BUG #15122: can't import data if table has a constraint with a function calling another function

>>>>> "Asier" == Asier Lostalé <asier(dot)lostale(at)openbravo(dot)com> writes:

Asier> Thanks Andrew for your quick response and clear explanation.

Asier> Can I understand from your explanation this is not considered as
Asier> a bug?

I would call it a misfeature rather than a bug.

Asier> Although the adding a qualified reference workarounds the
Asier> problem, it forces to write pl code that is aware of the schema
Asier> it is going to be imported in. How could I write this code to be
Asier> schema agnostic, so I can import it in any schema without
Asier> modifying it?

For plpgsql (and other pl/* languages, but not LANGUAGE SQL) the best
way is probably to do this:

SET search_path = public; -- or whatever schema

CREATE OR REPLACE FUNCTION is_even_positive(integer)
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
SET SEARCH_PATH FROM CURRENT -- ** this is the important bit
AS $$
begin
return is_even($1) and $1 >= 0;
end;
$$;

Some caveats:

1) The default search_path is "$user",public. Using SET SEARCH_PATH FROM
CURRENT doesn't interact well with this (arguably this part _is_ a
bug), so either ensure that the search_path is set to something that
doesn't exclude $user, or (if you need something that works in a
script) you can canonicalize it first using this query:

SELECT set_config('search_path',
string_agg(quote_ident(s),','),
false) -- change to true for equivalent of SET LOCAL
FROM unnest(current_schemas(false)) s;

2) This doesn't work well for LANGUAGE SQL functions since it would
block inlining, which is usually the primary reason for using
LANGUAGE SQL in the first place. I don't know of any good workaround
for those except to explicitly use the schema in the function body
(possibly via text substitution).

--
Andrew (irc:RhodiumToad)