Creating functions in SQL
Creating functions can be complex but like anything else, it becomes easier with practice. Let’s look through a few of the steps in detail:
- Begin with
CREATE OR REPLACE FUNCTION. This is where you name your function. - Next, define the function parameter(s) within parentheses.
- Insert the
RETURNS TABLEfunction, followed by the data type. This is a text option as the actual function will add the variable when it is run. - Now, select the
languageproperty of SQL as PostgreSQL since it is not limited by a single procedural language. - The actual query will now be included inside
$$ query $$. These are called dollar-quoted string constants ($$).
We need to create the function (as shown in the following code) and then pass to the function what we want it to do.
First, we want to select boundary_protected_area, which is where highways intersect. We are passing text (x text), which counts as one variable. The text is entered when...
