Skip to content
Advertisement

How to return custom record with calculated data

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.

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement