CODEWITHSUNDEEP
postgresqlfunctionstored-procedures
Ops
Ops

Reputation: 115

Postgres sql return value

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();

Upvotes: 0

Views: 1424

Answers (1)

danjuggler
danjuggler

Reputation: 1320

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();

Upvotes: 1

Related Questions