Variable column name on function

Question

I'm new to pgsql but have I have 8 years of experience with MSSQL, what i'm trying achieve is: create a function to apply this remove invalid data from names, it will remove all special characters, numbers and accents, keeping only spaces and a-Z characters, I want to use it on columns of different tables, but I cant really find what I'm doing wrong.

Here is my code:

CREATE OR REPLACE FUNCTION f_validaNome (VARCHAR(255)) 
  RETURNS VARCHAR(255) AS 
SELECT  regexp_replace(unaccent($1), '[^[:alpha:]\s]', '', 'g')
COMMIT

If I run

SELECT  regexp_replace(unaccent(column_name), '[^[:alpha:]\s]', '', 'g') 
from TableA

my code runs fine. I don't know exactly what is wrong with the function code.

Unrelated to your problem, but: Postgres 9.1 is no longer supported or maintained - you should plan an upgrade now. — user330315
– user330315, Commented Jan 27, 2021 at 21:17

score 3 · Accepted Answer · 2021-01-27 21:42:21Z

3

That's not how functions are written in Postgres.

As documented in the manual the function's body must be passed as a string and you need to specify which language the function is written in. Functions can be written in SQL, PL/pgSQL, PL/python, PL/perl and many others. There is also no need to reference parameters by position. Passing a dollar quoted string makes writing the function body easier.

For what you are doing, a simple SQL function is enough. It's also unnecessary to use an arbitrary character limit like 255 (which does have any performance or storage advantages over any other defined max length). So just use text.

CREATE OR REPLACE FUNCTION f_validanome (p_input text) 
  RETURNS text 
AS 
$body$ --<< string starts here. 
  SELECT regexp_replace(unaccent(p_input), '[^[:alpha:]\s]', '', 'g'); --<< required ; at the end
$body$ --<< string ends here
language sql
immutable; --<< required ; at the end

edited Jan 27, 2021 at 21:42

answered Jan 27, 2021 at 21:21

user330315

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

1 Comment

Erikson T F Over a year ago

Thank you, a_horse_with_no_name, your explanation along with the code did help me understand how to handle functions.

Collectives™ on Stack Overflow

Variable column name on function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related