SQLPage built-in functions

In addition to normal SQL functions supported by your database, SQLPage provides a few special functions to help you extract data from user requests.

These functions are special because they are not database functions. SQLPage evaluates them itself, either before or after the database query.

When a SQLPage function call is the whole value of a selected column, SQLPage first lets the database decide which rows exist. It selects the function arguments as hidden columns, then calls the SQLPage function once for each returned row. For example, SELECT sqlpage.url_encode(url) AS encoded FROM t runs the database query first, then applies url_encode to every returned url value. If the query returns no row, the function is not called. This also applies inside scalar SET subqueries, for instance SET body = (SELECT sqlpage.fetch(url) FROM cache_misses WHERE enabled).

In other positions, SQLPage functions run before the query, but only when their arguments can be evaluated without reading database columns. For instance, WHERE sqlpage.cookie('x') = '1' can run before the query, but WHERE sqlpage.fetch(url) IS NOT NULL cannot because url is a database column.

If a SQLPage function is expensive or has side effects and should run only once, store its result with SET first and reuse the variable. If a function should run only when a database row exists, put it as a standalone selected column in that row-producing query.

For more information about how SQLPage functions are evaluated, and data types in SQLPage, read the SQLPage data model documentation.