PL/pgSQL function that returns a table

create table staffs(
    staff_id int generated by default as identity primary key,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    email varchar(255) not null unique,
    phone varchar(25) not null,
    salary int not null
);

insert into staffs(first_name, last_name, email, phone, salary)
values ('Fabiola','Jackson','[email protected]','(831) 555-5554',1000000);
INSERT into staffs(first_name, last_name, email, phone,salary)
VALUES('Rahmad','Nasution','[email protected]','(831) 555-5554',12000000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Mireya','Copeland','[email protected]','(831) 555-5555',1000000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Genna','Serrano','[email protected]','(831) 555-5556',18000000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Virgie','Wiggins','[email protected]','(831) 555-5557',16000000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Jannette','David','[email protected]','(516) 379-4444',14500000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Marcelene','Boyer','[email protected]','(516) 379-4445',11000000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Venita','Daniel','[email protected]','(516) 379-4446',134000000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Kali','Vargas','[email protected]','(972) 530-5555',1900000);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Layla','Terrell','[email protected]','(972) 530-5556',130090);
INSERT INTO staffs(first_name, last_name, email, phone, salary)
VALUES('Bernardine','Houston','[email protected]','(972) 530-5557',1600000);


create or replace function get_staffs(pattern varchar)
returns table(staffs_email varchar,
staffs_salary int
)
as $$
begin
    return query select email, cast (salary as int)
    from staffs
    where email ilike pattern;
end; $$
language 'plpgsql';

select * from get_staffs('be%');

create or replace function staff(p_pattern varchar, p_salary int)
returns table (staffs_first_name varchar,
staffs_salary int
) as $$
declare
var_r record;
begin
    for var_r in(select first_name, salary
        from staffs
        where first_name ilike p_pattern and salary = p_salary)
loop
    staffs_first_name := upper (var_r.first_name);
    staffs_salary := var_r.salary;
    return next;
end loop;
end; $$
language 'plpgsql';

select * from staff('ra%', 12000000);

select * from staffs;

drop function staff