SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice?
It is common to use parameters in the WHERE part of queries, and to have some computed columns in the SELECT part, but not that common to parameterize the ORDER BY clause.
Let's say we have an application that lists pre-owned cars (à la CraigsList). The list of cars can be sorted by price or colour. we have one function that, given a certain amount of parameters (let's say price-range, colour, and sorting criteria for instance) returns a set of records with the results.
To make it concrete, let's assume
cars are all in the following table:
CREATE TABLE cars ( car_id serial NOT NULL PRIMARY KEY, /* arbitrary anonymous key */ make text NOT NULL, /* unnormalized, for the sake of simplicity */ model text NOT NULL, /* unnormalized, for the sake of simplicity */ year integer, /* may be null, meaning unknown */ euro_price numeric(12,2), /* may be null, meaning seller did not disclose */ colour text /* may be null, meaning unknown */ ) ;
The table would have indices for most columns...
CREATE INDEX cars_colour_idx ON cars (colour); CREATE INDEX cars_price_idx ON cars (price); /* etc. */
And have some commodity enumerations:
CREATE TYPE car_sorting_criteria AS ENUM ('price', 'colour');
... and some sample data
INSERT INTO cars.cars (make, model, year, euro_price, colour) VALUES ('Ford', 'Mondeo', 1990, 2000.00, 'green'), ('Audi', 'A3', 2005, 2500.00, 'golden magenta'), ('Seat', 'Ibiza', 2012, 12500.00, 'dark blue'), ('Fiat', 'Punto', 2014, NULL, 'yellow'), ('Fiat', '500', 2010, 7500.00, 'blueish'), ('Toyota', 'Avensis', NULL, 9500.00, 'brown'), ('Lexus', 'CT200h', 2012, 12500.00, 'dark whitish'), ('Lexus', 'NX300h', 2013, 22500.00, NULL) ;
The kind of queries we are going to make are of the style:
SELECT make, model, year, euro_price, colour FROM cars.cars WHERE euro_price between 7500 and 9500 ORDER BY colour ;
We would like to have queries of this style in a function:
CREATE or REPLACE FUNCTION get_car_list (IN _colour text, IN _min_price numeric, IN _max_price numeric, IN _sorting_criterium car_sorting_criteria) RETURNS record AS $BODY$ SELECT make, model, year, euro_price, colour FROM cars WHERE euro_price between _min_price and _max_price AND colour = _colour ORDER BY CASE WHEN _sorting_criterium = 'colour' THEN colour END, CASE WHEN _sorting_criterium = 'price' THEN euro_price END $BODY$ LANGUAGE SQL ;
Instead of this approach, the SQL in this function could be generated dynamically (in PL/pgSQL) as a string, and then EXECUTEd.
We can sense a few limitations, advantages and disadvantages with either approach:
- Within a function, finding which is the query plan for a certain statement is difficult (if possible at all). Yet we tend to use functions mostly when going to use something often enough.
- Errors in static SQL will be caught (mostly) when the function is compiled or when it is first called.
- Errors in dynamic SQL will only be caught (moslty) after the function is compiled, and all the execution paths have been checked (i.e.: the number of tests to perform to the function can be really high).
- A parametric query like the one exposed will probably be less efficient than a dynamically generated one; yet the executor will have a harder job parsing / making query tree / deciding every single time (which could impact efficiency in the opposite direction).
How to "get the best of both worlds" (if possible)? [Efficiency + Compiler checks + Debugging easily + Optimizing easily]
NOTE: this is to be run on PostgreSQL 9.6.