Alan Alan - 4 months ago 17
SQL Question

Finding Highest Value - PostgrelSql

I have a function which takes two values and returns setof record (postgre sql), as my assignment requires.

Now I have a different question, and I want to take this function but instead of returning a setof record, I want one of these optinos, whatever is easier:


  • Return a table instead of a setof.

  • Create a table before calling the function (table called "temp"), fill it with values.



Here's my function, which takes a month and a year as parameters and returns the payment a lawyer has to be given on the files he's worked on that month and year:

create or replace function calcbilling( cmonth int, cyear int) returns setof record
as $$
declare r record;
begin
for r in(select billing.fid, billing.lname, (lawyer.hbilling*billing.hours) as totpay
from billing natural join lawyer
where date_part('month',billing.bdate)=cmonth and date_part('year',billing.bdate)=cyear)
loop

return next r;
end loop;
end;
$$language plpgsql;


Thank you!

Answer

The function returning table is simpler (and much more handy of course) than original one:

create or replace function calc_billing(cmonth int, cyear int) 
returns table (fid integer, lname text, totpay numeric)
as $$
begin
    return query
        select b.fid, b.lname, l.hbilling* b.hours as totpay
        from billing b
        natural join lawyer l
        where 
            date_part('month', b.bdate) = cmonth 
            and date_part('year', b.bdate) = cyear;
end;
$$language plpgsql;

-- use:
select * from calc_billing(1, 2016);

The sql (instead of plpgsql) variant is even simpler:

create or replace function calc_billing(cmonth int, cyear int) 
returns table (fid integer, lname text, totpay numeric)
as $$
    select b.fid, b.lname, l.hbilling* b.hours as totpay
    from billing b
    natural join lawyer l
    where 
        date_part('month', b.bdate) = cmonth 
        and date_part('year', b.bdate) = cyear;
$$language sql;

Note, I have change the function name to make it easier to test (without names collision). Change the column types if necessary to fit your model.