5

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 ....
;
  • @ErwinBrandstetter: Instead I made a view and now I'm callingit it SELECT * FROM myView WHERE speed = 4 – Hans Dec 16 '14 at 10:12
4

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:

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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