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

Postgresql – Create Postgres function that concats and returns more than one row

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.

Tags:

2

Answers


  1. 0

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

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

Back To Top
Search