Writing Functions - PostgreSQL: Up and Running - page 148

Writing Functions with SQL
Although SQL is mostly a language for issuing queries, it can also be used to write
functions that run against the database. In PostgreSQL, doing so is fast and easy. Take
your existing SQL statements, add a functional header and footer, and you're done. The
ease comes at a price. You won't have fancy control languages to create conditional
execution branches. More restrictively, you can't run dynamic SQL statements that you
assemble on the fly depending on the arguments passed into the function.
On the positive side, the query planner can peek into an SQL function and optimize
execution depending on what it sees, a process called inlining . Planners treat other
languages as black boxes. Inlining lets SQL functions take advantage of indexes and
collapse repetitive computations.
Basic SQL Function
Example 8-2 shows a primitive SQL function that inserts a row into a table and returns
a scalar value.
Example 8-2. SQL function that returns the identifier of inserted record
CREATE OR REPLACE FUNCTION write_to_log ( param_user_name varchar , param_description
text )
RETURNS integer AS
$$
INSERT INTO logs ( user_name , description ) VALUES ( $ 1 , $ 2 )
RETURNING log_id ;
$$
LANGUAGE 'sql' VOLATILE ;
To call the function, execute something like:
SELECT write_to_log ( 'alejandro' , 'Woke up at noon.' ) As new_id ;
Similarly, you can update data with an SQL function and return a scalar or void, as
shown in Example 8-3 .
Example 8-3. SQL function to update a record
CREATE OR REPLACE FUNCTION
update_logs ( log_id int , param_user_name varchar , param_description text )
RETURNS void AS
$$
UPDATE logs SET user_name = $ 2 , description = $ 3
, log_ts = CURRENT_TIMESTAMP WHERE log_id = $ 1 ;
$$
LANGUAGE 'sql' VOLATILE ;
To execute:
SELECT update_logs ( 12 , 'alejandro' , 'Fell back asleep.' );