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?
CodePudding user response:
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