dinsdag 25 februari 2014

Why I like PostgreSQL more than MySQL: PL/PgSQL

MySQL supports stored functions, true. Unfortunately they only support the SQL-standard way, which is quite limited and not a very userfriendly language to write. (Isn't it ironic; the one time they do stick to the standard and they do it where they were better off using something custom) One of the most annoying limitations of the SQL standard PL language is that you cannot execute dynamic queries; queries that are composed in variables.

Dynamic queries allow you to build a query that suites the current requirements, depending on things like the content of the parameters, the content of the records and of course: business rules.

This can be particularly usefull for filterin and sorting. A function for fetchin the latest products may include a sortorder parameter that controls which field should be used to determine order, and that field will have to be added to the query in an ORDER BY clause. Obviously you could just create an ORDER-BY clause that uses a large CASE statement, but queryplanners cannot optimize this because they can't predict which field will be used.

In PostreSQL's PL/PgSQL you can include a command like this (from the manual)

        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);
As you can see it concatenates a series of strings with quoted variables and calls EXECUTE on it.
Effectively this means that your stored function can use complex logic to construct the query and execute it.

"Isn't that dangerous?"