Mastering PostgreSQL 9.6

The anatomy of a stored procedure

Before actually digging into a specific language, I want to talk a bit about the anatomy of a typical stored procedure. For demo purposes, I have written a function that just adds up two numbers:

test=# CREATE OR REPLACE FUNCTION mysum(int, int)                                                                                                                                                                 RETURNS int AS                                                                                                                                                                                                     '    SELECT $1 + $2;                                                                                                                                                   ' LANGUAGE 'sql'; CREATE FUNCTION 

The first thing you can see is that the procedure is written in SQL. PostgreSQL has to know which language we are using, so we have to specify that in the definition. Note that the code of the function is passed to PostgreSQL as a string ('). That is somewhat noteworthy because it allows a function to become a black box to the execution machinery. In other database engines, the code of the function ...