Add function parameter to SQL query WHERE clause

Question

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 ....
;

• Hans: @ErwinBrandstetter: Instead I made a view and now I'm callingit it SELECT * FROM myView WHERE speed = 4

Answer 1

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:

• The forgotten assignment operator "=" and the commonplace ":="
• Functions with variable number of input parameters

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

Answer 2

You can use Dynamic queries. Here is a page about it. Check out about 2/3rds of the way down after "Example 40-1. Quoting Values In Dynamic Queries". But like it states, you need to be VERY careful. Doing this type of SQL creation on the fly can be a huge hole for SQL injection.

http://www.postgresql.org/docs/current/static/plpgsql-statements.html