Variable Scope with CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial document, we will learn use of variable scope with CockroachDB and Python web applications. What you can expect in this lesson:

  • How and why? We will learn about how and where to define variables so that their scope is predictable in reach. We will also examine how variable scope works in both Python and CockroachDB. Finally, we will use both Python and Cockroach scripts samples throughout this lesson to show you how variable scope works in context.
  • What else? Because functions have a direct relationship to variable scope in both Python and Cockroach, we will take a brief look what a function is in each framework and how to define it.

Prerequisites

A beginner- or novice- level of understanding of how CockroachDB SQL works. You may wish to find an IDE or “database manager” application that allows you to manage your Cockroach database. Most of them include a tool to help you write SQL and most of them have a free version available.

Python functions overview

A Python function is a script that executes when referenced by another part of your application. You can send data to functions when you call them, or not. That optional data is known as a “parameter(s)”. You can have a function return data (or not). Functions are used for many purposes, including:

  • Boost coding and runtime efficiency. Rather than use ten lines of scripts for populating variables with data pulled from a Cockroach table, those ten lines of code can be placed in a function that you reference from every module that requires it and every time, you see “GetUserSecurityLevel()” instead of those ten lines of code multiple times.
  • Increase Readability. Boost scripts readability for yourself and anyone who inherits your application by giving your function a name that exemplifies the function’s purpose, like “GetUserSecurityLevel()”, for example.
  • Modularize the application. Within one module or page, you may want to do certain actions more than one time. Rather than scripts that set of actions more than once, introducing excess redundancy, added difficulty for maintenance, and error potential, you can refer to a function that is written once and lives in one spot. You may place a function in a file all by itself so you can refer to it from different modules or pages and can be easily added to other projects!

Python functions syntax

Syntax for a Function in Python:

1
2
def function_name(parameters):
    # scripts run by the function

Analysis

  • def: In Python we use the “def” keyword to initialize a function.
  • function_name: This is where we name a function. As mentioned above in the introduction, it is important to give the function a name that reflects how the function will be used or what problem it will solve.
  • parameters: This is OPTIONAL data you give your function.

Note: Functions can return data or merely attain an operation.

Example of use of a Python function

1
2
3
4
def Pluralize(txtSourceString):
    return txtSourceString + "s"

print (Pluralize("Brother"))

The above example will print “Brothers” because the function we named “Pluralize” will take a source string and add an “s” character to the end of it using concatenation.

Variable Scope in Python

1
2
3
4
5
6
7
8
txtVariable_1 = 'X'

def change_value(txtVariable_2):
    txtVariable_1 = txtVariable_2

print(txtVariable_1)
change_value('Z')
print(txtVariable_1)

Notice in the scripts above the first thing we did – outside of the function – is to set the value of “txtVariable_1” to be “X”. The next thing that happens is “print(txtVariable_1)”, which – as you might expect – prints “X”. Now is where you might be surprised: You would expect after the “change_value(‘Z’)”, that the value of “txtVariable_1” would become “Z”, right? But it stays “X”. The reason is because the “txtVariable_1” inside of our “change_value” function is treated by Python as a whole other variable. You could call it “local to the function” where the “txtVariable_1” declared outside the function as a “global variable”.

So how do we allow the function to modify our global variable? We put the key word “global” before the variable, like you see in the following script:

1
2
3
4
5
6
7
8
txtVariable_1 = 'X'

def change_value(txtVariable_2):
    global txtVariable = txtVariable_2

print(txtVariable_1)
change_value('Z')
print(txtVariable_1)

Now your results will look like:

1
2
X
Z

Functions for Cockroach

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION name_of_my_function (parameters)
RETURNS return_datatype AS $names_of_variables$
   DECLARE
      -- declaration of variables here;

   BEGIN
      -- scripts goes here
      RETURN { my_variable_name | VALUE }
   END;
   LANGUAGE plpgsql;

So that is the syntax of a CockroachDB function. Let’s set up a larger program that includes a function and weave in some variables, so we can see how scope works in Cockroach.

Before we continue onward, it’s important to note that Cockroach doesn’t have built-in global variables. In order to get around that issue, we will create a function called “globalized” and show you how to use it:

1
2
3
4
CREATE OR REPLACE FUNCTION globalized(txtVarName text)
RETURNS INTEGER LANGUAGE SQL AS $$
    SELECT current_setting('globalized.' || txtVarName)::INTEGER;
$$;

What we’ve written above is defined a custom configuration parameter of “globalized” and tacked it on to the starting point of any variables we want to act like a global variable in the rest of our SQL program.

Let’s write some more SQL that will utilize what we’ve created:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Declare "local" variables and one "global"
DECLARE
SET globalized.idUser := 12;
intStart INT := 0;
intIncrement INT := 1;
intCurrent INT := 1;
intEnd INT := 15;
-- Tell Cockroach the boundaries of our program, nested in a BEGIN..END container.
BEGIN
    -- Define our loop within a LOOP..END LOOP container.
    WHILE intCurrent <= intEnd
        LOOP
            -- Code to be executed. In this case, we are doing two things:
            -- Incrementing our intCurrent variable.
            intCurrent := intCurrent + intIncrement;
            -- Printing the value of local variable: intCurrent.
            RAISE NOTICE intCurrent;
            -- Printing the value of our one global variable.
            RAISE NOTICE globalized('idUser');
        END LOOP;
END;

Look for future tutorial documents where we make more in-depth use of different variable scopes in both Python and CockroachDB.

Conclusion

We took a brief look at how both Python and Cockroach functions work, so that we could then move on to learning about variable scope in Python and Cockroach. The basic idea being that sometimes you will want to keep variables isolated between a function or functions and the rest of your application and other times you’ll want your function to be able to change the value in a “global” variable. In Cockroach it works slightly different, as there are no global variables, but there will be times when you will want to have a more global-type variable in CockroachDB that acts much like a session variable, per user. Finally, we listed the source scripts here in each part of this instructional article.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.