Division between postgresql functions

Question

I have two functions that, when performed separately, return me values without problem. Turns out I need to divide one by the other but it’s not working.

Função 1
select getestoque(array[19480000001], array[19480000001], '2019-03-01')

Retorno
183

Função 2
select avg(pel_area) from getarea(array[19480000001,19480000002], array[19480000001, 19480000002], '2019-03-01')

Retorno
75.000

Another detail, this division needs to be done within a third function, I’m doing it this way:

Função 3
create or replace function getLotacao(fazenda bigint[], retiro bigint[], dataLot date, intervalo bigint)
returns 
table (
    totalCabecas integer,
    pesoTotal decimal(18, 6),
    UA decimal(15, 6),
    pesoMedio decimal(18, 6),
    valorMedio decimal(18, 6),
    total decimal(18, 6),
    areaHec decimal(18, 6),
    cabHec decimal(18, 6), 
    UAHA decimal(18, 6),
    areaAql decimal(18, 6),
    cabAlq decimal(18, 6), 
    UAAlq decimal(18, 6)
) as $$
declare
begin   
    for i in 0..$4 -1 loop
    return query 
    select getestoque($1, $2, $3::date + 1 * i),
           getpesolotacao($1, $2, $3::date + 1 * i),
           getpesolotacao($1, $2, $3::date + 1 * i) / 450,
           getpesolotacao($1, $2, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i),
           getvalorlotacao($1, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i),
           getpesolotacao($1, $2, $3::date + 1 * i) * (getvalorlotacao($1, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i)),
           avg(pel_area) from getarea($1, $2, $3::date + 1 * i),
           getestoque($1, $2, $3::date + 1 * i) / avg(pel_area) from getarea($1, $2, $3::date + 1 * i),
           **(getpesolotacao($1, $2, $3::date + 1 * i) / 450) / avg(pel_area) from getarea($1, $2, $3::date + 1 * i)**,
           avg(pel_area) from getarea($1, $2, $3::date + 1 * i) / 2.4,
           getestoque($1, $2, $3::date + 1 * i) / (avg(pel_area) from getarea($1, $2, $3::date + 1 * i) / 2.4),
           (getpesolotacao($1, $2, $3::date + 1 * i) / 450) / (avg(pel_area) from getarea($1, $2, $3::date + 1 * i) / 2.4);                                                                                                       
    end loop;                                                                                                                                                                                                                   
end;
$$ language plpgsql;

The problem is in:

**(getpesolotacao($1, $2, $3::date + 1 * i) / 450) / avg(pel_area) from getarea($1, $2, $3::date + 1 * i)**,

ERROR: syntax error at or near "/" LINE 28: getstock($1, $2, $3::date + 1 * i) / avg(pel_area) fr...

But it’s not working, can help me please?

Thank you.

Answer 1

You should only make a clause from and assign aliases to each of the "tables", which are actually called functions that return a table. However it doesn’t seem to make much sense this query, you need to use the clause from even?

I recommend if possible not use as many functions and yes Views, seems more correct for your case, search more on Views in Postgresql. The performance advantage of View is that it is a dynamic read, you create a View and instead of passing parameters uses Where clause normally.

Another important thing is to search about JOIN LATERAL or simply LATERAL, in this way it is possible to perform data processing at the level of Views.