Postgresql – Make query depend on input in PostgreSQL function

parameterplpgsqlpostgresql

I have a simple query:

CREATE OR REPLACE FUNCTION companies_all_teams(
  IN  _company_name text = NULL,
  IN  _company_id int = NULL,
  OUT _result text
)
AS
$$
  BEGIN
  _result := (SELECT json_agg(distinct(teams.name))
                     FROM jobs
                     JOIN companies
                       ON companies.id = jobs.company_id
                     JOIN teams
                       ON teams.id = jobs.team_id
                     WHERE lower(companies.name) = lower(_company_name)
                       AND jobs.is_disabled = false
             );
  END;
$$ LANGUAGE plpgsql;

There are two inputs, but only one of those is being used in the query. What is the simplest way to run the query conditionally based on which input we have?

Best Answer

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:

Related Question