MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for February, 2020

PostgreSQL Table Function

without comments

This shows how to write a PL/pgSQL function that returns a filtered table result set while writing to a debug log file. The example requires a small character table, like:

DROP TABLE IF EXISTS CHARACTER;
CREATE TABLE CHARACTER
( character_id    SERIAL
, character_name  VARCHAR );

and, a logger table:

DROP TABLE IF EXISTS logger;
CREATE TABLE logger
( logger_id     SERIAL
, message_text  VARCHAR );

Now, let’s insert a couple rows into the character table. The following query inserts one row:

INSERT INTO CHARACTER
( character_name )
VALUES
('Harry Potter');

It was simply too much fun to write this tricky insert statement to the character table. It only submits a value when it doesn’t already exist in the set of character_name column values. While it eliminates the need for a unique constraint on the character_name column, it makes every insert statement more costly in terms of machine resources.

WITH cte AS
( SELECT 'Harry Potter' AS character_name
  UNION ALL
  SELECT 'Hermione Granger' AS character_name
  UNION ALL
  SELECT 'Ronald Weasily' AS character_name )
INSERT INTO CHARACTER
( character_name )
( SELECT character_name
  FROM   cte
  WHERE  NOT EXISTS
          (SELECT NULL
           FROM   CHARACTER c
           WHERE  c.character_name = cte.character_name));

You can verify these insert statements work with the following query:

SELECT * FROM CHARACTER;

It returns:

 character_id |  character_name  
--------------+------------------
            1 | Harry Potter
            2 | Hermione Granger
            3 | Ronald Weasily
(3 rows)

The following character_query PL/pgSQL function filters table results and returns a table of values. The function defines the future query return results, which is a full fledged object-oriented programming adapter pattern.

CREATE OR REPLACE
  FUNCTION character_query (pattern VARCHAR)
  RETURNS TABLE ( character_id    INTEGER
                , character_text  VARCHAR ) AS
$$
BEGIN
  RETURN QUERY
  SELECT c.character_id
  ,      c.character_name
  FROM   CHARACTER c
  WHERE  c.character_name SIMILAR TO '%'||pattern||'%';
END;
$$ LANGUAGE plpgsql;

You can test the character_query function, like this:

SELECT * FROM character_query('Hermione');

It returns:

 character_id |  character_text  
--------------+------------------
            2 | Hermione Granger
(1 row)

Building on what we did, let’s log our query word in the logger table. You add an insert statement after the BEGIN keyword and before the RETURN QUERY phrases, like:

CREATE OR REPLACE
  FUNCTION character_query (pattern VARCHAR)
  RETURNS TABLE ( character_id    INTEGER
                , character_text  VARCHAR ) AS
$$
BEGIN
  INSERT INTO logger
  ( message_text )
  VALUES
  ( pattern );
 
  RETURN QUERY
  SELECT c.character_id
  ,      c.character_name
  FROM   CHARACTER c
  WHERE  c.character_name SIMILAR TO '%'||pattern||'%';
END;
$$ LANGUAGE plpgsql;

Now let’s test the new character_query function with this test case:

SELECT * FROM character_query('Ron');

Then, let’s check the logger table with this query:

SELECT * FROM logger;

It displays:

 logger_id | message_text 
-----------+--------------
         1 | Hermione
(1 row)

As always, I hope this helps those looking for a solution.

Written by maclochlainn

February 21st, 2020 at 12:58 am

Python List & Dictionaries

without comments

The following two sample programs are used in an Python programming course that I teach. I find them useful in qualifying how to work with loops, couple loops, and queues. The first example uses two lists and coupled loops, while the second example uses a single dictionary and FILO queue approach.

The Twelve Days of Christmas lyrics can be printed like so with coupled loops:

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
26
27
days = ['first','second','third','fourth'       \
       ,'fifth','sixth','seventh','eighth'      \
       ,'nineth','tenth','eleventh','twelveth']
 
verse = ['partridge in a pear tree.'     \
        ,'Two turtle doves,'             \
        ,'Three French hens,'            \
        ,'Four calling birds,'           \
        ,'Five gold rings,'              \
        ,'Six geese a-laying,'           \
        ,'Seven swans a-swimming,'       \
        ,'Eight maids a-milking,'        \
        ,'Nine ladies dancing,'          \
        ,'Tenth lords a-leaping,'        \
        ,'Eleven pipers piping,'         \
        ,'Twelve drummers drumming,']
 
# Loop forward, couple inner loop, and loop backward through list.
for i in range(0,len(days), 1):
  print("On the",str(days[i]),"day of Christmas my true love sent to me")
  for j in range(i, -1, -1):
    if (j > 0):
      print(" ",verse[j])
    elif (i == j):
      print("  A",verse[j])
    else:
      print("  and a",verse[j])

Recreating the problem into a single dictionary, you can solve by approaching it as a FILO queue. Here’s the approach:

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
26
27
28
29
30
31
32
lyric = {'first':'partridge in a pear tree.'
        ,'second':'Two turtle doves,'
        ,'third':'Three French hens,'
        ,'fourth':'Four calling birds,'
        ,'fifth':'Five gold rings,'
        ,'sixth':'Six geese a-laying,'
        ,'seventh':'Seven swans a-swimming,'
        ,'eighth':'maids a-milking,'
        ,'nineth':'Nine ladies dancing,'
        ,'tenth':'Ten lords a-leaping,'
        ,'eleventh':'Eleven pipers piping,'
        ,'twelfth':'Twelve drummers drumming,'}
 
# Intiate a list for collecting stanza.
stanza = list()
 
# Generate a list of keys.
for i in lyric.keys():
  # Append keys to list of stanza.
  stanza.append(i)
 
  # Print the first line of each stanza.
  print("On the",i,"day of Christmas my true love sent to me")
 
  # Print the progressive stanza.
  for j in reversed(stanza):
    if (j not in ['first','twelveth']):
      print(" ",lyric[j])
    elif (i == j):
      print("  A",lyric[j])
    else:
      print("  and a",lyric[j])

As always, I hope this helps for approaches and solutions.

Written by maclochlainn

February 16th, 2020 at 12:42 am

Posted in Python,Python 3.x

Tagged with

Developing Python Libraries

without comments

I put this together to show my students how to simplify writing and testing Python library files. The trick requires that you learn how to set a relative $PYTHONPATH environment file.

export set PYTHONPATH=./lib

After setting the $PYTHONPATH environment variable, connect to Python’s IDLE environment and run the following code:

import os
print(os.environ['PYTHONPATH'])

It prints the following:

./lib

You can also discover all the standard libraries and your $PYTHONPATH value in your environment with the following command:

for i in sys.path:
  print(i)

It prints the following, which lists the one set by the $PYTHONPATH first:

/home/student/Code/python/path/lib
/usr/lib64/python37.zip
/usr/lib64/python3.7
/usr/lib64/python3.7/lib-dynload
/home/student/.local/lib/python3.7/site-packages
/usr/lib64/python3.7/site-packages
/usr/lib/python3.7/site-packages

You create a test my_module.py library file in the relative ./lib directory, like the following:

# Define a hello function that accept a name and prints a salutation.
def hello(whom):
  return "Hello " + whom + "!"

Next, you can create a testlib.py program:

# Import the hello function into the local namesapce from the my_module.
from my_module import hello
 
# Call the module hello, which returns a formatted string.
print(hello("Suzie Q"))

It imports the hello(whom) function into the local namespace and then calls the hello(whom) function with the string literal "Susie". It prints:

Hello Suzie Q!

If you import the my_module module, you must refer to the hello(whom) function by prefacing it with my_module., like the following example:

# Import the hello function into the local namesapce from the my_module.
import my_module
 
# Call the module hello, which returns a formatted string.
print(my_module.hello("Suzie Q"))

A direct import doesn’t add the method to the local namespace. It remains in the my_module‘s namespace.

It’s probably important to note where my_module.pyc files are written for the those migrating from Python 2.7 to Python 3. In Python 2.7 they would be written to the ./lib directory, but in Python 3 they’re written to the ./lib/__pycache__ directory.

As always, I hope this helps those who find it and read it.

Written by maclochlainn

February 13th, 2020 at 12:23 am