![]() ![]() NOTE: this is to be run on PostgreSQL 9.6.įirst, I want to address ambiguity in the premise:Ĭomputed columns in the SELECT part are hardly ever relevant for the query plan or performance. How to "get the best of both worlds" (if possible)? 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).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).Errors in static SQL will be caught (mostly) when the function is compiled or when it is first called.Yet we tend to use functions mostly when going to use something often enough. Within a function, finding which is the query plan for a certain statement is difficult (if possible at all).We can sense a few limitations, advantages and disadvantages with either approach: ![]() Instead of this approach, the SQL in this function could be generated dynamically (in PL/pgSQL) as a string, and then EXECUTEd. IN _sorting_criterium car_sorting_criteria)Įuro_price between _min_price and _max_priceĬASE WHEN _sorting_criterium = 'colour' THENĬASE WHEN _sorting_criterium = 'price' THEN We would like to have queries of this style in a function: CREATE or REPLACE FUNCTION get_car_list The kind of queries we are going to make are of the style: SELECT and some sample data INSERT INTO cars.cars (make, model, year, euro_price, colour) CREATE INDEX cars_colour_idxĪnd have some commodity enumerations: CREATE TYPE car_sorting_criteria AS ENUM The table would have indices for most columns. Year integer, /* may be null, meaning unknown */Įuro_price numeric(12,2), /* may be null, meaning seller did not disclose */Ĭolour text /* may be null, meaning unknown */ Model text NOT NULL, /* unnormalized, for the sake of simplicity */ Make text NOT NULL, /* unnormalized, for the sake of simplicity */ To make it concrete, let's assume cars are all in the following table: CREATE TABLE carsĬar_id serial NOT NULL PRIMARY KEY, /* arbitrary anonymous key */ 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. The list of cars can be sorted by price or colour. Let's say we have an application that lists pre-owned cars (à la CraigsList). 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. Queries like 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? ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |