Module lessons (1/4)
UDFs and base SQL
So far we've been sending queries from the Node.js server. But if a formula is used in dozens of different queries (e.g. computing the final price including VAT), why duplicate the math in JavaScript? SQL Functions (User Defined Functions - UDF) let us create "macros" inside the DB itself.
Here is how to create a mathematical SQL macro:
CREATE OR REPLACE FUNCTION calculate_tax(price NUMERIC)
RETURNS NUMERIC AS $$
SELECT price * 1.22;
$$ LANGUAGE sql;Once this command runs, the function lives forever in our database. It can be called from any query:
SELECT name, calculate_tax(unit_price) AS price_with_tax FROM products;Dynamic creation
We can define as many variables as we want, specifying their types, and LANGUAGE sql will run the query and implicitly return the last statement placed between the dollar signs $$.
Try it yourself
Create a simple callable function named 'get_full_name' that accepts two TEXT parameters ('first_name' TEXT, 'last_name' TEXT).\nIt must return a TEXT value.\nInside, do a trivial SELECT that concatenates the two arguments with a single space between them, and declare it as language sql.
Show hint
CREATE OR REPLACE FUNCTION get_full_name(first_name TEXT, last_name TEXT) RETURNS TEXT AS $$ SELECT first_name || ' ' || last_name; $$ LANGUAGE sql;
Solution available after 3 attempts
In more advanced UDFs we can embed entire queries. Create a function 'get_customer_count()' that accepts no parameters and RETURNS an INTEGER.\nInside, run a trivial 'SELECT COUNT(id) FROM customers'.\nDeclare it as pure sql language.
Show hint
Watch out: COUNT returns a bigint, cast it to INTEGER to match the return type! CREATE OR REPLACE FUNCTION get_customer_count() RETURNS INTEGER AS $$ SELECT CAST(COUNT(id) AS INTEGER) FROM customers; $$ LANGUAGE sql;
Solution available after 3 attempts