1

For an API I am building I am supposed to write a function with a single hstore argument containing colname => value pairs so that queries may be filtered with a WHERE clause including an indefinite amount of subclauses. I currently have the following function definition (not a MWE):

CREATE OR REPLACE FUNCTION api.func( conds HSTORE )
RETURNS TABLE (LIKE api.tab) AS 
$$
BEGIN
    RETURN QUERY
        SELECT * 
        FROM api.tab 
        WHERE -- conds here
        LIMIT 25 ;
END;
$$ LANGUAGE plpgsql;

I am not really sure how to continue. Could someone please give me a hint?

2
  • Hint: dynamic SQL.
    – mustaccio
    Commented Aug 8, 2022 at 12:10
  • @mustaccio: In the sense of building the WHERE clause as a string and then format it into the main query statement which I then run with EXECUTE?
    – eslukas
    Commented Aug 8, 2022 at 15:47

1 Answer 1

0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.