I try take all records from two columns with different tables and Divide Between each other but i have only result from first row not from all rows How i can fix that?
CREATE FUNCTION human() RETURNS integer AS $$
DECLARE person integer;
size integer;
def integer;
Begin
Select sizex into size from region;
Select defx into def from humans;
osoba = def / size;
return person;
END;
$$ LANGUAGE 'plpgsql';
select human();
1 Answer 1
Assuming your "humans" table and "region" table both have an ID field. And also assuming your humans table has a regionId field creating a relationship between your two tables, I would suggest doing the following:
CREATE FUNCTION human(arg_humanId int) RETURNS decimal AS $$
DECLARE
div_result decimal;
Begin
SELECT h.defx/r.sizex
INTO result
FROM humans h
JOIN region r on r.ID = h.RegionID
WHERE h.ID = arg_humanId;
RETURN div_result;
END;
$$ LANGUAGE 'plpgsql';
SELECT human(h.ID)
FROM humans h;
Note I've changed the data type from integer to decimal since division usually results in decimal places.
Another option is to return a setof decimals and do all of the logic inside your function:
CREATE FUNCTION human() RETURNS setof decimal AS $$
DECLARE
div_result decimal;
Begin
RETURN QUERY
SELECT h.defx/r.sizex
FROM humans h
JOIN region r on r.ID = h.RegionID;
END;
$$ LANGUAGE 'plpgsql';
SELECT *
FROM human();
answered Dec 22, 2016 at 18:13
danjuggler
1,3402 gold badges21 silver badges40 bronze badges
Sign up to request clarification or add additional context in comments.
Comments
Explore related questions
See similar questions with these tags.
lang-sql