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.