My task is to create a function that concatenates the first name and last name from two fields found on the same table.

Table "cust":

And here is my function:

CREATE OR REPLACE FUNCTION fn_concat(f_name character varying, l_name character varying)
RETURNS character varying
AS
$$
BEGIN
    IF cust.f_name IS NULL AND cust.l_name IS NULL THEN
        RETURN NULL;
    ELSEIF cust.f_name IS NULL AND cust.l_name IS NOT NULL THEN
        RETURN cust.l_name;
    ELSEIF cust.f_name IS NOT NULL AND cust.l_name IS NULL THEN
        RETURN cust.f_name;
    ELSE
        RETURN CONCAT(cust.f_name, ' ', cust.l_name);
    END IF;
END;
$$
LANGUAGE plpgsql;

When I run that code I get no errors and the function is successfully created in the functions table. Here is the query that I have used to test that newly created function:

SELECT * FROM fn_concat(f_name, l_name);

This is what my newly created function and test query should be returning:

Table "cust":

But instead, I get an error, and here is that error:

ERROR: column "f_name" does not exist
LINE 1: SELECT * FROM fn_concat(f_name, l_name); 
      ^ 
    
SQL state: 42703 
Character: 30

Thank you very much for any help that is offered.

CodePudding user response：

Here is the function

CREATE OR REPLACE FUNCTION fn_concat(arg_f_name text, arg_l_name text)
RETURNS text as
$$
  select trim(concat(arg_f_name, ' ', arg_l_name));
$$ language SQL;

Then call it like this

select *, fn_concat(f_name, l_name) from cust;

CodePudding user response：

The function call needs to be part of the SELECT list:

select cus_id, fn_concat(f_name, l_name), email
from cust

Btw: there is already a function that does exactly what you want: concat_ws()

select cus_id, concat_ws(' ', f_name, l_name), email
from cust