Create Postgres function that concats and returns more than one row-CodePudding

I have played around with this for a few hours and it seems to be going over my head.

I am a novice at SQL and am attempting to create a simple function to concat first_name and last_name rows into a staff_fname table.

Using this code returns the results just fine and populates staff_fname, concatenating each first name and last name entry from the source table:

SELECT
CONCAT (first_name, ' ', last_name) INTO staff_fname
FROM staff;

However, when I try to insert this into a function, it does not work properly:

CREATE FUNCTION connames()
RETURNS varchar(90)
LANGUAGE plpgsql
AS $$
DECLARE staff_fname varchar(90);
BEGIN
    SELECT
    CONCAT (first_name, ‘ ’, last_name) INTO staff_fname
    FROM staff;

    RETURN staff_fname;
END;
$$;

When I try to call the function connames(), it only concats and returns one row (the first row). I have tried playing around with LOOP, RETURNS SETOF, and RETURN QUERY to no avail. I am sure it is simple but I can't figure it out.

CodePudding user response：

Should be simple using language SQL

create or replace function connames() returns setof text as 
$$
  select concat(first_name, ' ', last_name) from staff;
$$
language sql stable;

CodePudding user response：

It would be like

create or replace function connames()
returns setof text as 
$$
begin
 return query select CONCAT (first_name,' ', last_name) FROM staff;
end;
$$ language plpgsql;

and execution

select connames() into staff_fname;