If I understand correctly, you can sometimes provide _company_id directly, while you only have _company_name in other situations. You do not intend to provide both at once.
Since one parameter is of type text while the other is integer the most efficient solution for this case is probably function overloading. I.e., create two functions of the same name, one for text and another one for integer.
While being at it, I suggest simple SQL functions for the simple task. The principal is the same as for plpgsql.
json_agg() returns data type json, not text. Your plpgsql variant silently coerces the type in the assignment, but that's bad style. I made out the functions to return json directly. You can cast to text explicitly if you need to.
1) companies_all_teams(text)
CREATE OR REPLACE FUNCTION companies_all_teams(_company_name text)
RETURNS json AS
$func$
SELECT json_agg(DISTINCT(t.name))
FROM companies c
JOIN jobs j ON j.company_id = c.id
JOIN teams t ON t.id = j.team_id
WHERE lower(c.name) = lower(_company_name)
AND NOT j.is_disabled
$func$ LANGUAGE sql;
2) companies_all_teams(int)
Since we have two functions, we can optimize each for their input. Assuming referential integrity in your relational design, we don't need to join to companies at all, we already know the company_id:
CREATE OR REPLACE FUNCTION companies_all_teams(_company_id int)
RETURNS json AS
$func$
SELECT json_agg(DISTINCT(t.name))
FROM jobs j
JOIN teams t ON t.id = j.team_id
WHERE j.company_id = _company_id
AND NOT j.is_disabled
$func$ LANGUAGE sql;
To avoid ambiguity with overloaded function calls you need to provide explicitly typed values. In your simple case, however, it's still enough to use numeric literals (no single quotes) for integer or string literals (single quotes) for text.
Call:
SELECT companies_all_teams(123); -- numeric literal without quotes
SELECT companies_all_teams('My Company'); -- string literal with quotes
Related:
