I want to return a custom empRecord row. How can I do this? Please do not change the select statement. Essentially I just want to return the name of an employee and a new rank.
x
CREATE TABLE employees (
id integer primary key
name text,
rank integer
);
create type empRecord as (
name text,
new_rank integer
);
INSERT INTO employees (id, name, rank) VALUES (1, 'james', 5), (2, 'bob', 10), (3, 'Cole', '54');
create or replace function
testFunc() returns empRecord
as $$
declare
nrank integer;
r empRecord;
begin
for r in select * from employees
loop
/*
if (r.rank > 50) THEN
nrank = r.rank + 30
elsif (r.rank > 10) THEN
nrank = r.rank + 20
else (r.rank > 5) THEN
nrank = r.rank + 2
*/
return next (r.name, nrank) -- This part seems to be wrong
end loop;
end;
$$ language plpgsql;
Table I want:
+-------+------+
| name | rank |
+-------+------+
| james | 7 |
| bob | 30 |
| cole | 84 |
| TOTAL | 121 |
+-------+------+
Advertisement
Answer
A more typical use of a function would be something like this:
CREATE OR REPLACE FUNCTION NEW_RANK(original_rank)
RETURNS int AS $$
BEGIN
-- put add'l logic here if you want to do if/then statements, etc.
RETURN original_rank + 5;
END;
$$ LANGUAGE plpgsql;
And then you invoke it like this:
select name, NEW_RANK(rank) from employees;
But note the function takes a single input and responds with a single output. That is typically how developers use SQL functions in my experience.