Procedures in TimescaleDB

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

Introduction

In this lesson, we see to create Procedures in TimescaleDB, including an explanation of input and output parameters. We will include:

  • What? What do TimescaleDB database’s procedures do and what’s the syntax?
  • Why? Why use the procedure construct, otherwise understandn as Store Procedures in some other relational database systems?
  • How? How and why do we use TimescaleDB Procedures, where do we use them in our SQL scripts, and how do we refer to them from within Python scripts?

The first thing to do is set up a database instance. We prefer using Objectrocket because we like the amount of value and efficiency they provide.

Create a TimescaleDB Database Instance on ObjectRocket

  • (1) Go to kb.objectrocket.com and “Log In”.
  • (2) Use left menu to “Create Instance”.
  • (3) In the “Name” field, add a descriptive name for your instance.
  • (4) Under “Select your service” choose the system you want to use from “CockroachDB, Elasticsearch, PostgreSQL, Redis, TimescaleDB, and MongoDB”.
  • (5) Choose the “Cloud Provider” and “Type” you want.
  • (6) Select “Version” and “Region”.
  • (7) Click the “GO TO STEP 2” button.
  • (8) Make a choice in the “Pick Your Flavor” section.
  • (9) Choose the “Capacity (Storage/Memory)”. Notice how this choice influences your total fee at the bottom right.
  • (10) Click the “GO TO STEP 3” button.
  • (11) Under “Add a Whitelist IP” pick “ALOW ANY IP” or “USE MY IP” or “Add my IPs later”.
  • (12) Choose between “Master” and “Replica” and click the “ADD” button.
  • (13) Now click the “CREATE INSTANCE” button at the bottom.

Before we start building queries, let’s look at how to use Flask’s psycopg2 to connect to a TimescaleDB instance at ObjectRocket.

Connect to TimescaleDB

1
2
3
4
5
6
7
8
9
t_dbname = "myTSdb"
t_name_user = "tsdbadmin"
t_password = "secret"
t_sslmode = "require"
t_host = "ingress.hkybrhnz.launchpad.objectrocket.cloud"
t_port = "4129"
connection_string = "dbname=" & t_dbname & " user=" & t_name_user & " password=" & t_password & " host=" & t_host & " port=" & t_port & " sslmode=" & sslmode
db_conn = psycopg2.connect(connection_string)
db_cursor = db_conn.cursor()

What’s a TimescaleDB Procedure?

Some features of Procedures:

  1. We can also refer to Procedures as “stored procedures” (from MSSQL), “named programs” that are kept in our database, as opposed to being kept in our application’s code. In some ways, this can increase portability and modularity.
  2. Compiled object.
  3. Error checked.
  4. Procedures may or may not use argument values (parameters).
  5. Default for TimescaleDB Procedures is to return no values, differing here from Functions. Most likely, you’ll be giving your Procedure a value or values, but expecting no return value, unless there is an error.
  6. Execution can be explicit or implicit: Explicit execution. EXECUTE, along with specific SP name and optional parameters; and Implicit execution using only the name of the Procedure.
  7. Can not be run or called from within a SELECT SQL statement.
  8. You can call a TimescaleDB Procedure as often as you like.

Why TimescaleDB Procedures?

  • Modularity. When you’re writing duplicate-ish queries more than once within your code, if you want more efficiency, you can write just one kept procedure that – possibly with a parameter or more – can be run as many times as you like from your code, with far less scripting, potential typos, and future maintenance. Not much different than the benefits of using functions.
  • Performance. A Procedure’s code is compiled when created, meaning no want to compile at run-time, unless you change the program (Kept Procedure). This means much faster execution than interpreted SQL!
  • Safety. Procedures reduce potential for hacking, including one of the most popular, SQL injection.

Creating TimescaleDB Procedures

For much of this document, we’ll examine how to create a TimescaleDB Procedure specifically using TimescaleDB’s structured query language (SQL). Let us begin with the syntax.

TimescaleDB Procedure Syntax

1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE [PROCEDURE name]([optional:params GO here])
AS
DECLARE
BEGIN
    [execution; your "program" code]
EXCEPTION [optional]
    [optional: IF an error; error handling]
END;

TimescaleDB Procedure Parameters

In terms of input and output to and from our TimescaleDB Procedure, there are 3 types of parameters:

  • IN (only input).
  • IN OUT (in/out mode – input and output).
  • OUT (only output).

TimescaleDB Procedure Use

Let us start with a simple table called “tblTechnology” in our TimescaleDB database system. We’ll use this table to track various technologies used at our firm.

Notice the following: We’re assuming the idTech column is an auto-incrementing index in this TimescaleDB table.

idTechtxtNametxtCategory
0PostgresRDB
1MongoNoSQL
2TimescaleDBRDB
3PythonLanguage
4PHPLanguage
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE PROCEDURE ADD_A_USER
    (
    P_txtName IN tblTechnology.txtName%TYPE,
    P_txtCategory IN tblTechnology.txtCategory%TYPE,
    P_txtMsgError OUT VARCHAR2
    )
IS

DECLARE
-- No variables to declare in this procedure.

BEGIN
    INSERT INTO
    tblTechnology
    (
     txtName
     , txtCategory
    ) VALUES (
     P_txtName
     , P_txtCategory
    COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            P_txtMsgError := SQLERRM;
END ADD_USER;

Code Analysis

Let’s do an in-depth examination of the above SQL:

  • CREATE: CREATE is used to add objects (like tables and functions) to our database. Here, we recommend getting into the practice of adding “OR REPLACE” to account for overwrites. In other words, odds are high you’ll need to make changes to this TimescaleDB Procedure from time to time.
  • ADD_A_USER: The naming of our TimescaleDB Procedure. This is important because we require a name to use for our Python (or whatever language) code to execute the TimescaleDB Procedure. Here we named the TimescaleDB Procedure “ADD_A_USER”.
  • PARAMETERS: In parentheses immediately following the name of our TimescaleDB Procedure are the parameters we chose to use with our TimescaleDB Procedure, if any. Notice how we named all parameter variables with a “P_” appended? This is A MUST. Notice how 2 of our Procedure’s parameters are “IN” and one is “OUT”? The “IN” parameters are what are sent to the SP from our application and the “OUT” parameter is what our TimescaleDB Procedure sends back to our program, if anything. We will give an example of this later.
  • %TYPE: If we don’t understand the parameter variable type, we can use a placeholder called %TYPE to declare the type of the parameter to be dynamic in nature and thus, more flexible. This approach will reduce safety but increase flexibility.
  • IS: This can also be “AS”. No difference. They gave us this option for easier compatibility and simplicity of understanding if we’re coming from other databases like SQL Server, MySQL, etc., that use “AS”.
  • DECLARE: The DECLARE here has no arguments in its block, which is because this is an optional area and for our purposes with this little TimescaleDB Procedure, we don’t want to declare any variables.
  • BEGIN: This is where we tell TimescaleDB the starting of what we want executed on TimescaleDB, all the way up to the END statement at the bottom.
  • INSERT INTO: Here we’re telling our database engine, “We are about to give you values to put into the following two columns. Following is the order they go in.
  • VALUES: This is to instruct TimescaleDB what values to place in those columns we defined above. Notice the “P” appending our two parameter values? Scroll up some in the TimescaleDB Procedure code to be reminded how, in the PARAMETERS area we named each parameter with a “P” before it.
  • COMMIT: Instruct TimescaleDB to actually follow the commands we gave it above this place in the SQL.
  • EXCEPTION: In this script block, we tell TimescaleDB what to do if an error is detected.
  • WHEN OTHERS THEN: Here is the only piece of this TimescaleDB Procedure that actually sends data back out to our calling code.
  • END: Marks the end of the BEGIN block and end of execution.

Calling a TimescaleDB Procedure

Because Python works so well with TimescaleDB, we’ll use this language for the example below.

1
2
3
4
5
6
7
8
9
10
from flask import Flask # popular python library
import psycopg2 # for database connection and dependent on Flask
app = Flask(__name__)
crdbConn = psycopg2.connect(t_dsn)
crdbCursor = crdbConn.cursor()
crdbCursor.callproc('ADD_A_USER', ('Python','Language'))
txtResults = crdbCursor.fetchone()
crdbCursor.close()
crdbConn.close()
print("Results: " + txtResults)

Here – if we assume the connection and cursor objects are not something we want to recreate and destroy over and over – we have three lines of code taking the place of Python, .Net, Java, etc. code that builds the following SQL statement:

1
2
3
4
5
6
7
8
9
INSERT INTO
tblTechnology
(
    txtName
    , txtCategory
) VALUES (
    'Python'
    , 'Language'
COMMIT;

Plsimplicity notice that the SQL statement above does not include the TRY…COMMIT…EXCEPT block of error-checking code that may look like this:

1
2
3
4
5
6
crdbCursor.execute(s)
try:
    crdbConn.commit()
except psycopg2.Error as txtErrorMsg:
    txtMessage = "Database error: " + txtErrorMsg + "/n SQL: " + s
    print(txtMessage)

Next, consider writing those INSERT INTO blocks of SQL over and over throughout your application but only changing what you send into those two parameters? Do you see how much time and potential for errors you can save by using TimescaleDB’s Procedure structure instead?

Extras

  • The Procedure’s declarations can be empty.
  • Procedures without parameters are called “static procedures”.
  • TimescaleDB Procedures that use parameters are called “dynamic”.

Conclusion

Here we will learned why and how to create a Procedure in TimescaleDB to give us more control, efficiency, speed, modularity, simplicity, and safety. We also looked at how to call a Procedure from Python. Code samples included.

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.