Problem with PostgreSQL function parameter of type “name”

I’m writing some PostgreSQL functions to compile a list of search terms for records in a table called name. Since the search terms come from multiple columns on the name table and from multiple columns on other tables, a simple generated column isn’t sufficient.

Here’s a simplified version of the function.

CREATE OR REPLACE FUNCTION compile_name_search_terms(n name) RETURNS text AS $$
BEGIN
  return n.id || ' ' ||
    COALESCE(n.full_name, '') || ' ' ||
    COALESCE(n.phone, '') || ' ' || 
    regexp_replace(COALESCE(n.phone, ''), '[()-]', '', 'g');
END
$$ LANGUAGE plpgsql;

Attempting to execute

SELECT id, compile_name_search_terms(t) FROM name AS t;

throws error

ERROR:  function compile_name_search_terms(public.name) does not exist
LINE 1: SELECT id, compile_name_search_terms(t) FROM name AS t;
                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 12

I have working functions for other tables customer, vendor, etc. but cannot figure out the syntax for the name table. I suspect the problem stems from the fact that the table name is a non-reserved keyword.

I don’t have the liberty to rename the name table as this is in production.

What syntax do I need to make this work?

Answer

There is a built-in data type called name which (being located in pg_catalog) has a higher precedence than your table’s type.

So the parameter type is actually pg_catalog.name.

To avoid the ambiguity you need to declare the parameter as a full qualified name: public.name

Advertisement

Answer