Calling postgres function with multiple params

Question

This is my sql function in postgresql:

FUNCTION test(year integer)
  RETURNS SETOF json AS
$BODY$
    SELECT  ARRAY_TO_JSON(ARRAY_AGG(T))
    FROM table t
    WHERE year = $1;
$BODY$

This works quite good. But now I want specify more parameters and I'd like to get a return with the condition if parameters are set. For example following function call:

test(year := 2014, location := 'Belo Horizonte')

How should the function look like and where to set conditions? Here is my (wrong) suggestion:

FUNCTION test(year integer, location text)
      RETURNS SETOF json AS
    $BODY$
        SELECT  ARRAY_TO_JSON(ARRAY_AGG(T))
        FROM table t
        IF $1 IS SET THEN
        WHERE year = $1
        ELSIF $2 THEN
        UNION
        WHERE location = $2
        END IF;
    $BODY$

A further challenge is a return of the function for this statements:

test(year := 1584)
-- should return all entries with year 1584

test(location := 'Cambridge')
-- should return all entries with location Cambridge

test(year := 1584, location := 'Cambridge')
-- should return all entries with year 2014 AND location Belo Horizonte

Thanks in advance!

Answer 1

You may try to do something like that, adding default values, and working with OR clauses

FUNCTION test(year integer DEFAULT -1, location text DEFAULT 'noLocation')
      RETURNS SETOF json AS
    $BODY$
      SELECT  ARRAY_TO_JSON(ARRAY_AGG(T))
        FROM table t
        WHERE ($1 = -1 OR  year = $1)
        AND   ($2 = 'noLocation' OR  location = $2);
    $BODY$