Question 90021 in PostgreSQL Question posted in
The official documentation can be found here.

Creating a function that will combine varchar text from two fields on the same table – Postgresql

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":

cus_id f_name l_name email
1 Jack Daniels [email protected]
2 Jose Quervo [email protected]
5 Jim Beam [email protected]

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":

cus_id fn_concat email
1 Jack Daniels [email protected]
2 Jose Quervo [email protected]
5 Jim Beam [email protected]

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search