Unable to create function in postgresql

Question

CREATE OR REPLACE FUNCTION normalize(input text, separator text DEFAULT '')
RETURNS text AS $$
BEGIN
    RETURN translate(lower(public.f_unaccent(input)), ' '',:-`´‘’_' , separator);
END
$$ LANGUAGE 'plpgsql' IMMUTABLE;

When i execute i get the following error. I tried dos2unix but didn't help

ERROR: syntax error at or near "(" LINE 1: CREATE OR REPLACE FUNCTION normalize(input text, separator t...

`normalize` is a [Key Word](https://www.postgresql.org/docs/15/sql-keywords-appendix.html): *NORMALIZE non-reserved (cannot be function or type) reserved reserved* — Adrian Klaver, Nov 24 '22 at 21:55
this an old code and it apparently worked in the past. Now we are running PostgreSQL 13.4. Something has changed recently? Can you please point me to the documentation that says 'normalize' is a key word. Thanks! — Ramkumar Ramagopalan, Nov 24 '22 at 21:57
I gave you the link in my comment. Looks like 13 is where it became reserved for functions. — Adrian Klaver, Nov 24 '22 at 21:59
Seems it is a built in function [String functions](https://www.postgresql.org/docs/13/functions-string.html): *normalize ( text [, form ] ) → text Converts the string to the specified Unicode normalization form. ...* — Adrian Klaver, Nov 24 '22 at 22:09
@AdrianKlaver You should make this an answer so other people can benefit from it, too. — SebDieBln, Nov 24 '22 at 23:29

Erwin Brandstetter · Answer 1 · 2022-11-25T06:29:28.303

Like @Adrian commented, normalize is a reserved word in standard SQL. But it used to be allowed anyway until Postgres 13, where a system function of the same name was added. The release notes:

Add SQL functions NORMALIZE() to normalize Unicode strings, and IS NORMALIZED to check for normalization (Peter Eisentraut)

"normalize" changed its status to:

non-reserved (cannot be function or type)

While being at it, I suggest:

CREATE OR REPLACE FUNCTION f_normalize (input text, separator text DEFAULT '')
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
  BEGIN ATOMIC
SELECT lower(public.f_unaccent(translate(input, $$',:-`´‘’_$$, separator)));
END;

Most importantly, make it PARALLEL SAFE (because it is) or you may regret it. See:

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

And STRICT, since all used functions are strict themselves - assuming that for your f_unaccent().

BEGIN ATOMIC requires Postgres 14 or later. (Else make it a conventional SQL function.) See:

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

Also, since translate() is the cheapest operation, I would apply that first for a tiny overall gain.

Finally, if your f_unaccent() function something like this, you might just add the additional operations into a single function instead of creating another wrapper.

Unable to create function in postgresql

1 Answers1