Filter a SELECT query with predicates passed as single hstore value

Pure SQL

Can even be done with pure SQL and no function:

SELECT * 
FROM   api.tab t
WHERE  hstore(t.*) @> '"b"=>"bar", "a"=>2'  -- your hstore here
LIMIT  25;

fiddle

hstore(t.*) converts the whole row to hstore.
@> is the hstore containment operator, which can be supported with a GIN or GiST index.

But this can only use an expression index on the whole row (which might get big and inefficient for wide rows):

CREATE INDEX tab_special_idx ON api.tab USING GIN (hstore(api.tab.*));

SQL function

The same in an SQL function wrapper:

CREATE OR REPLACE FUNCTION api.func(conds hstore)
  RETURNS SETOF api.tab
  LANGUAGE sql STRICT AS 
$func$
SELECT * 
FROM   api.tab t
WHERE  hstore(t.*) @> $1
LIMIT  25;
$func$;

Call:

SELECT * FROM api.func('"b"=>"bar", "a"=>2');

fiddle

PL/pgSQL with dynamic SQL

CREATE OR REPLACE FUNCTION api.func(conds hstore)
  RETURNS SETOF api.tab
  LANGUAGE plpgsql STRICT AS 
$func$
BEGIN
   RETURN QUERY EXECUTE
      concat_ws(
        E'\n'
      , 'SELECT * FROM api.tab'
      , (SELECT 'WHERE ' || string_agg(format('%I = %L', key, value), ' AND ') FROM each($1))
      , 'LIMIT  25'
      );
END
$func$;

Same call.

fiddle

This can use any index as usual.

It's carefully drafted to be safe against SQL injection. Only adds a WHERE clause if there are filters.

Either of the functions is defined as STRICT, so returns NULL on NULL input. An empty hstore input value returns the unfiltered table. each() produces no row, so no WHERE clause is added; concat_ws() concatenates NULL-safely.

Related:

• How to use function parameters in dynamic SQL with EXECUTE?
• PostgresSQL dynamic execute with argument values in array