Archive for February, 2020
PostgreSQL Table Function
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.
Python List & Dictionaries
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.
Developing Python Libraries
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.
Subscribe via RSS