Postgresql – Add function parameter to SQL query WHERE clause

dynamic-sqlfunctionsjavapostgresql

I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function:

CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS 
$$
    SELECT .... FROM ...
    JOIN ...
    where true
    ;
$$
LANGUAGE SQL;

I want the Function parameter INPUTS to also be in the WHERE clause so if inputs is

AND speed = 0 AND ....

Where clause looks like

where true AND speed = 0 AND ...

How can i achieve this?

EDIT

Also it is acceptable to have many parameters (a int, b string, c string ..) but then i need to have

WHERE speed = * AND stop = * AND ...

which is not acceptable. How can i achieve this ?

OR can i put a if statement inside it ? Like

Select .. . from ...
JOIN ... 
WHERE true
IF (a != null){AND speed = $1}
IF ....
;

Best Answer

If you don't always pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL:

CREATE OR REPLACE FUNCTION func(_a int  = NULL
                              , _b text = NULL
                              , _c text = NULL)
  RETURNS TABLE(...) AS
$func$
    SELECT ... FROM ...
    WHERE (speed = $1 OR $1 IS NULL)
    AND   (stop  = $2 OR $2 IS NULL)
    ...
$func$  LANGUAGE sql;

Now you can call the function with any number of parameters using named notation:

SELECT * FROM func(_c => 'foo', _a => 123);  -- no _b, which defaults to NULL

Note, the assignment operator in the call is => (or := for Postgres 9.4 or older), not =!
See:

Aside: "string" is not a data type, text is.

Much more is possible with dynamic SQL with EXECUTE in a plpgsql function.

More comprehensive answer on SO with complete recipes:

Test for is null or = value in function

Related Solutions

Postgresql – Return ResultSet with column names from functions

Consider the example below:

CREATE TABLE test.child (
    id    integer,
    p_id1 integer,
    p_id2 integer
);

CREATE OR REPLACE FUNCTION named_cols() 
RETURNS SETOF test.child 
AS $$
  SELECT 0, 12, 23;                                                                                                                                                                                                 
$$ LANGUAGE sql;

SELECT named_cols();
 named_cols 
────────────
 (0,12,23)


SELECT * FROM named_cols();
 id │ p_id1 │ p_id2 
────┼───────┼───────
  0 │    12 │    23

If you call the function the first way, you will get back a record, without the column names. It looks like you are after the second one - you would experience the same behaviour with any programming language, not only Java.

Postgresql – Naming conflict between function parameter and result of JOIN with USING clause

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or at the start of the function definition, declaring how you want such conflicts to be resolved.
The 3 possible settings are error (the default), use_variable and use_column:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;

Best Answer

Related Solutions

Postgresql – Return ResultSet with column names from functions

Postgresql – Naming conflict between function parameter and result of JOIN with USING clause

Related Question