iterate over select statement, return data in rows & custom total row

user_10461806 0
Viewed 39

I want to return a specific table like so.

+-------------+------+-------+--------------+
|  full_name  | mark | grade | good_student |
+-------------+------+-------+--------------+
| mike smith  |   42 | F     | N            |
| sam logan   |   90 | A     | Y            |
| TOTAL MARKS |  132 | NULL  | NULL         |
+-------------+------+-------+--------------+

Could someone please show me how I can set specific data for each column in each row. For example good_student is a custom data I set with the logic if mark is greater than 50 then Y, else N. As well as return a custom row at the end which has the TOTAL MARKS, which is the sum of the mark.

I am trying to understand how to write my own if statements and return custom set rows of data but getting very lost. Please do not change the SQL select statement I provided in my PLPGSQL function. I made it like that on purpose so I can understand the PLPGSQL part better.

CREATE TABLE students (
  id integer primary key,
  first_name text,
  last_name text,
  mark integer,
  grade text
);

INSERT INTO students (id, first_name, last_name, mark, grade) VALUES
(1, 'mike', 'smith', 42, 'F'),
(2, 'sam', 'logan', 90, 'A');

CREATE TYPE ClassRecord as (
  full_name text,
  mark integer,
  grade text,
  good_student char(1) -- Y if mark above 50, N if mark below 50
);

CREATE OR REPLACE FUNCTION
   generateClassRecord()
   RETURNS ClassRecord
AS $$

BEGIN
    FOR ROW IN SELECT * FROM students;
    -- Unsure how to access things now and return specific data
END

$$ language plpgsql
plpgsql postgresql sql database
1 Answers

A case expression returns the last column and union all the last row:

select s.*
from ((select s.full_name, s.mark, s.grade,
              (case when s.grade > 50 then 'Y' else 'N') as good_student
       from students s
      ) union all
      (select 'TOTAL MARKS', sum(s.mark), null, null
       from students
      )
     ) s
order by (full_name = 'TOTAL MARKS')::int desc;
user_11440350
Related