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

>>>>> "Asier" == Asier Lostalé <asier.lostale@openbravo.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)