specific column name on postgres function return

Question

In postgres I have a custom function that will join first and last name.

  CREATE FUNCTION full_name(u users) RETURNS varchar
  LANGUAGE plpgsql VOLATILE
  AS $$
    BEGIN
      RETURN CONCAT_WS(' ', u.first_name, u.last_name);
    END;
  $$;

It would be nice if I didn't have to set the the name of the column name and it was determined by the function eg.

eg. here I have to say the column name is full_name

SELECT
 full_name(users) as full_name

But it would be nice if it atomically name it full_name

SELECT
full_name(users)

Is this possible to set in a custom function?

score 2 · Accepted Answer · 2016-02-14 09:18:43Z

2

As the function parameter is the name of the table, you don't have to pass it.

You can call it like this:

select users.full_name
from users;

Note that you have to prefix the function name with the table name. You can also use an alias, but you still need the prefix.

select u.full_name
from users u;

In that case the column from the result set will be named full_name (the name of the function)

Btw: you don't need PL/pgSQL for the function. A plain SQL will likely be faster for this. Eespecially when you declare it stable instead of volatile - the it can be inlined and the overhead of calling the function is eliminated.

edited Feb 14, 2016 at 9:18

answered Feb 14, 2016 at 8:28

user330315

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

Comments

Abelisto · Accepted Answer · 2016-02-14 10:29:26Z

1

Try to use out parameter instead of returning the value:

CREATE FUNCTION full_name(in u users, out full_name varchar)
LANGUAGE plpgsql VOLATILE
AS $$
  BEGIN
    full_name := CONCAT_WS(' ', u.first_name, u.last_name);
  END;
$$;

and

SELECT * FROM full_name(users)

answered Feb 14, 2016 at 10:29

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Comments

Mesbah Gueffaf · Accepted Answer · 2016-02-14 08:08:02Z

0

This could be a solution to your problem. Instead of passing users as parameter you can use the first_name and last_name as parameters to the function.

CREATE OR REPLACE FUNCTION full_name(character varying ,character varying)
 RETURNS character varying AS
$BODY$
declare
    l_nom ALIAS FOR $1;
    l_pnom ALIAS FOR $2;
begin

    RETURN CONCAT_WS(' ', l_nom, l_pnom);    
 end;$BODY$
 LANGUAGE plpgsql VOLATILE
COST 100;
     ALTER FUNCTION full_name(character varying ,character varying) OWNER TO postgres;

SELECT full_name(u.first_name, u.last_name)

answered Feb 14, 2016 at 8:08

Mesbah Gueffaf

5581 gold badge7 silver badges23 bronze badges

Collectives™ on Stack Overflow

specific column name on postgres function return

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related