Problem with PostgreSQL function parameter of type "name"

Question

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?

WOW! of all the things I tried, why didn't I think of that? That works. Make it an answer and I'll accept it. — StillLearnin
– StillLearnin, Commented Oct 6, 2021 at 12:28

user330315 · Accepted Answer · 2021-10-06 12:30:46Z

1

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

answered Oct 6, 2021 at 12:30

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with PostgreSQL function parameter of type "name"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related