I'm trying to write a function that conditionaly prunes out data based on parameters supplied. I want to be able to use default parameters so that if they aren't explicity supplied the function doesn't do any filtering. The first of two filtering types is temporal ranges, and the second is an array of primary keys.
For the temporal ranges I can use the default sentinel variables +/- infinity. This matches all the data, and in essence disables the filter. These are provided by postgres. Is there anything (a sentinel of some sort) I can use to disable the array based filter ?
Specifically i'd pass the function an array of integers, but I want to disable this filter by default (--i.e., when I don't pass the array). The obvious choice is to set the array to an empty array as a default parameter. However, where in with an empty array would match nothing (provided an empty array is even valid plpgql). So the obvious choice is to have an IF statement, or dynamic SQL. But knowing postgres there has to be some trick or syntax I might have missed.
edit:
My initial plan was to write a boolean UDF to emulate the behaviour I wanted. Something along the lines of :
CREATE OR REPLACE FUNCTION filter_category(
category_array INTEGER[],
current_category INTEGER
)
RETURNS
BOOLEAN AS $$
BEGIN
IF category_array = '{}' THEN
RETURN TRUE;
ELSE
-- if in set return true, else false.
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql;
However, After some further thought on the matter, I think the query will have to get more complex. A where in array clause, or the function above, will probably not be very efficient. So to end up using any indices that might be present I'll have to unnest the array of primary keys and perform a join.
And I should only do this when the array is not null. So the query will need at least one `IF ... ELSE... END IF' statement. And if I need to do further filtering I might have to implement intermediary local datasets. Still I'm interested in any advice :D
edit 2:
My dynamic query looks like this:
CREATE OR REPLACE FUNCTION get_paginated_search_results(
forum_id_ INTEGER,
query_ CHARACTER VARYING,
from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
DECLARE
join_string CHARACTER VARYING := ' ';
from_where_date CHARACTER VARYING := ' ';
to_where_date CHARACTER VARYING := ' ';
query_string_ CHARACTER VARYING := ' ';
BEGIN
IF NOT from_date_ IS NULL THEN
from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
END IF;
IF NOT to_date_ IS NULL THEN
to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
END IF;
CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;
if in_categories_ != '{}' THEN
join_string := ' INNER JOIN forum_topics ft ON fp.topic_id = ft.id ' ||
' INNER JOIN un_cat uc ON uc.id = ft.category_id ' ;
END IF;
query_string_ := '
SELECT index,posted_at,post_text,name,join_date,quotes
FROM forum_posts fp
INNER JOIN forum_user fu ON
fu.forum_id = fp.forum_id AND fu.id = fp.user_id' ||
join_string
||
'WHERE fu.forum_id = ' || forum_id_ || ' AND
to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' ||
from_where_date ||
to_where_date
||';';
RAISE NOTICE '%', query_string_ ;
RETURN QUERY
EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;