MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PostgreSQL 14’ Category

PL/pgSQL List to Struct

without comments

This blog post addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/pgSQL it’s an array of a type). The cast_strings function converts a list of strings into a record data structure. It calls the verify_date function to identify a DATE data type and uses regular expressions to identify numbers and strings.

You need to build the struct type below first.

CREATE TYPE struct AS
( xnumber  DECIMAL
, xdate    DATE
, xstring  VARCHAR(100));

The cast_strings function is defined below:

CREATE FUNCTION cast_strings
( pv_list  VARCHAR(10)[] ) RETURNS struct AS
  $$
  DECLARE
  /* Declare a UDT and initialize an empty struct variable. */
  lv_retval  STRUCT := (null, null, null); 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..ARRAY_LENGTH(pv_list,1) LOOP
      /* Order if statements by evaluation. */
      CASE
        /* Check for a value with only digits. */
        WHEN lv_retval.xnumber IS NULL AND REGEXP_MATCH(pv_list[i],'^[0-9]+$') IS NOT NULL THEN
          lv_retval.xnumber := pv_list[i];
        /* Check for a valid date. */
        WHEN lv_retval.xdate IS NULL AND verify_date(pv_list[i]) IS NOT NULL THEN
          lv_retval.xdate := pv_list[i];
        /* Check for a string with characters, whitespace, and digits. */
        WHEN lv_retval.xstring IS NULL AND REGEXP_MATCH(pv_list[i],'^[A-Za-z 0-9]+$') IS NOT NULL THEN
          lv_retval.xstring := pv_list[i];
        ELSE
          NULL;
      END CASE;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
$$ LANGUAGE plpgsql;

There are two test cases for the cast_strings function. One uses a DO-block and the other a query.

  • The first use-case checks with a DO-block:

    DO
    $$
    DECLARE
      lv_list    VARCHAR(11)[] := ARRAY['86','1944-04-25','Happy'];
      lv_struct  STRUCT;
    BEGIN
      /* Pass the array of strings and return a record type. */
      lv_struct := cast_strings(lv_list);
     
      /* Print the elements returned. */
      RAISE NOTICE '[%]', lv_struct.xnumber;
      RAISE NOTICE '[%]', lv_struct.xdate;
      RAISE NOTICE '[%]', lv_struct.xstring;
    END;
    $$;

    It should return:

    psql:verify_pg.SQL:263: NOTICE:  [86]
    psql:verify_pg.SQL:263: NOTICE:  [1944-04-25]
    psql:verify_pg.SQL:263: NOTICE:  [Happy]

    The program returns a structure with values converted into their appropriate data type.

  • The second use-case checks with a query:

    WITH get_struct AS
    (SELECT cast_strings(ARRAY['99','2015-06-14','Agent 99']) AS mystruct)
    SELECT (mystruct).xnumber
    ,      (mystruct).xdate
    ,      (mystruct).xstring
    FROM    get_struct;

    It should return:

     xnumber |   xdate    | xstring
    ---------+------------+----------
          99 | 2015-06-14 | Agent 99
    (1 row)

    The query defines a call to the cast_strings function with a valid set of values and then displays the elements of the returned structure.

As always, I hope this helps those looking for how to solve this type of problem. Just a quick reminder that this was written and tested in PostgreSQL 14.

PL/pgSQL Date Function

with 2 comments

This post provides an example of using PostgreSQL’s REGEXP_MATCH function, which works very much like the REGEXP_LIKE function in Oracle and a verify_date function that converts a string data type to date data type.

Here’s a basic function to show how to use a generic REGEXP_MATCH function:

1
2
3
4
5
6
7
8
9
10
11
DO
$$
DECLARE
  lv_date_in  DATE := '2022-10-22';
BEGIN
 
  IF (REGEXP_MATCH('2022-10-02','^[0-9]{4,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL) THEN
    RAISE NOTICE '[%]', 'Truth';
  END IF;
END;
$$;

The following is a verify_date function, which takes a string with the ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format and returns a BOOLEAN true or false value.

CREATE FUNCTION verify_date
  ( IN pv_date_in  VARCHAR(10)) RETURNS BOOLEAN AS
  $$
  DECLARE
    /* Local return variable. */
    lv_retval  BOOLEAN := FALSE;
  BEGIN
    /* Check for a YYYY-MM-DD or YYYY-MM-DD string. */
    IF REGEXP_MATCH(pv_date_in,'^[0-9]{2,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL THEN
 
      /* Case statement checks for 28 or 29, 30, or 31 day month. */
      CASE
        /* Valid 31 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 31) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 31) THEN 
          lv_retval := TRUE;
 
        /* Valid 30 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 30) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 30) THEN 
          lv_retval := TRUE;
 
        /* Valid 28 or 29 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND SUBSTRING(pv_date_in,6,2) = '02') OR
             (LENGTH(pv_date_in) =  8 AND SUBSTRING(pv_date_in,4,2) = '02') THEN
          /* Verify 4-digit year. */
          IF (LENGTH(pv_date_in) = 10 AND
              MOD(TO_NUMBER(SUBSTRING(pv_date_in,1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 29) OR
             (LENGTH(pv_date_in) =  8 AND
              MOD(TO_NUMBER(SUBSTRING(TO_CHAR(TO_DATE(pv_date_in,'YYYY-MM-DD'),'YYYY-MM-DD'),1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 29) THEN
            lv_retval := TRUE;
          ELSE /* Not a leap year. */
            IF (LENGTH(pv_date_in) = 10 AND
                TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 28) OR
               (LENGTH(pv_date_in) = 8 AND
                TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 28)THEN
              lv_retval := TRUE;
            END IF;
          END IF;
       NULL;
      END CASE;
    END IF;
 
    /* Return date. */
    RETURN lv_retval;
  END;
$$ LANGUAGE plpgsql;

The following four SQL test cases:

SELECT verify_date('2020-07-04') AS "verify_date('2020-07-04')";
SELECT verify_date('71-05-31')   AS "verify_date('71-05-31')";
SELECT verify_date('2024-02-29') AS "verify_date('2024-02-29')";
SELECT verify_date('2019-04-31') AS "verify_date('2019-04-31')";

Return the following:

 verify_date('2020-07-04')
---------------------------
 t
(1 row)
 
 
 verify_date('71-05-31')
-------------------------
 t
(1 row)
 
 
 verify_date('2024-02-29')
---------------------------
 t
(1 row)
 
 
 verify_date('2019-04-31')
---------------------------
 f
(1 row)

As always, I hope the example code fills somebody’s need.

Written by maclochlainn

May 25th, 2022 at 1:47 am

PL/pgSQL Coupled Loops

without comments

I love a challenge. A loyal Oracle PL/SQL developer said PL/pgSQL couldn’t support coupled loops and user-defined lists. Part true and part false. It’s true PL/pgSQL couldn’t support user-defined lists because it supports arrays. It’s false because PL/pgSQL supports an ARRAY_APPEND function that lets you manage arrays like Java’s ArrayList class.

Anyway, without further ado. You only need to create one data type because PL/pgSQL supports natural array syntax, like Java, C#, and other languages and doesn’t adhere rigidly to the Information Definition Language (IDL) standard that Oracle imposes. Oracle requires creating an Attribute Data Type (ADT) for the string collections, which you can avoid in PL/pgSQL.

You do need to create a record structure type, like:

/* Create a lyric object type. */
CREATE TYPE lyric AS
( day   VARCHAR(8)
, gift  VARCHAR(24));

You can build a function to accept an array of strings and an array of record structures that returns a new array constructed from parts of the two input arrays. The function also compares and matches the two arrays before returning an array that combines strings for a songs lyrics. While the example uses the ever boring 12 Days of Christmas, I’d love another for examples. It just needs to use this type of repetitive structure. If you have one that you would like to share let me know.

The twelve_days function is:

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
CREATE FUNCTION twelve_days
  ( IN pv_days   VARCHAR(8)[]
  , IN pv_gifts  LYRIC[] ) RETURNS VARCHAR[] AS
$$  
DECLARE 
  /* Initialize the collection of lyrics. */
  lv_retval  VARCHAR(36)[114];
BEGIN
  /* Read forward through the days. */
  FOR i IN 1..ARRAY_LENGTH(pv_days,1) LOOP
    lv_retval := ARRAY_APPEND(lv_retval,('On the ' || pv_days[i] || ' day of Christmas')::text);
    lv_retval := ARRAY_APPEND(lv_retval,('my true love sent to me:')::text);
 
    /* Read backward through the lyrics based on the ascending value of the day. */
    FOR j IN REVERSE i..1 LOOP
      IF i = 1 THEN
        lv_retval := ARRAY_APPEND(lv_retval,('-'||'A'||' '|| pv_gifts[j].gift)::text);
      ELSIF j <= i THEN
        lv_retval := ARRAY_APPEND(lv_retval,('-'|| pv_gifts[j].day ||' '|| pv_gifts[j].gift )::text);
      END IF;
    END LOOP;
 
    /* A line break by verse. */
    lv_retval := ARRAY_APPEND(lv_retval,' '::text);
  END LOOP;
 
  /* Return the song's lyrics. */
  RETURN lv_retval;
END;
$$ LANGUAGE plpgsql;

Then, you can test it with this query:

SELECT UNNEST(twelve_days(ARRAY['first','second','third','fourth'
                          ,'fifth','sixth','seventh','eighth'
                          ,'nineth','tenth','eleventh','twelfth']
                         ,ARRAY[('and a','Partridge in a pear tree')::lyric
                          ,('Two','Turtle doves')::lyric
                          ,('Three','French hens')::lyric
                          ,('Four','Calling birds')::lyric
                          ,('Five','Golden rings')::lyric
                          ,('Six','Geese a laying')::lyric
                          ,('Seven','Swans a swimming')::lyric
                          ,('Eight','Maids a milking')::lyric
                          ,('Nine','Ladies dancing')::lyric
                          ,('Ten','Lords a leaping')::lyric
                          ,('Eleven','Pipers piping')::lyric
                          ,('Twelve','Drummers drumming')::lyric])) AS "12-Days of Christmas";

It prints:

       12-Days of Christmas
----------------------------------
 On the first day of Christmas
 my true love sent to me:
 -A Partridge in a pear tree
 
 On the second day of Christmas
 my true love sent to me:
 -Two Turtle doves
 -and a Partridge in a pear tree
 
 On the third day of Christmas
 my true love sent to me:
 -Three French hens
 -Two Turtle doves
 -and a Partridge in a pear tree
 
... Redacted for space ...
 
On the twelfth day of Christmas
 my true love sent to me:
 -Twelve Drummers drumming
 -Eleven Pipers piping
 -Ten Lords a leaping
 -Nine Ladies dancing
 -Eight Maids a milking
 -Seven Swans a swimming
 -Six Geese a laying
 -Five Golden rings
 -Four Calling birds
 -Three French hens
 -Two Turtle doves
 -and a Partridge in a pear tree

So, I believe that I met the challenge and hopefully provided a concrete example of some syntax that seems to be missing from most of the typical places.

Written by maclochlainn

May 16th, 2022 at 1:32 am

Record Type Arrays

with one comment

Another question that I was asked today: “Can you create an array of a record type in PL/pgSQL?” The answer is yes.

You first have to create a type, which is what you do when you want to create a table with an embedded table. This is a simple full_name record type:

CREATE TYPE full_name AS
( first_name   VARCHAR(20)
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20));

The following DO block shows you how to create a record type array and then print it’s contents in a FOR-LOOP:

DO
$$
DECLARE
  -- An array of full_name records.
  list  full_name[] = 
          array[('Harry','James','Potter')
               ,('Ginevra','Molly','Potter')
               ,('James','Sirius','Potter')
               ,('Albus','Severus','Potter')
               ,('Lily','Luna','Potter')];
BEGIN
  -- Loop through the integers.
  FOR i IN 1..CARDINALITY(list) LOOP
    RAISE NOTICE '%, % %', list[i].last_name, list[i].first_name, list[i].middle_name;
  END LOOP;
END;
$$;

Since you typically only have a single dimension array with record-type structure, using CARDINALITY is clearer than ARRAY_LENGTH(list,1). If you don’t agree use the latter.

It prints the following:

NOTICE:  Potter, Harry James
NOTICE:  Potter, Ginevra Molly
NOTICE:  Potter, James Sirius
NOTICE:  Potter, Albus Severus
NOTICE:  Potter, Lily Luna
DO

As always, I hope this helps those looking for a solution to this type of problem.

Multidimension Arrays

without comments

Picking up where I left off on yesterday’s post on PostgreSQL arrays, you can also write multidimensional arrays provided all the nested arrays are equal in size. You can’t use the CARDINALITY function to determine the length of nested arrays, you must use the ARRAY_LENGTH to determine the length of subordinate arrays.

Here’s an example file with a multidimensional array of integers:

DO
$$
DECLARE
  /* Declare an array of integers with a subordinate array of integers. */
  list  int[][] = array[array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]];
  row   varchar(20) = '';
BEGIN
  /* Loop through the first dimension of integers. */
  <<Outer>>
  FOR i IN 1..ARRAY_LENGTH(list,1) LOOP
    row = '';
    /* Loop through the second dimension of integers. */
    <<Inner>>
    FOR j IN 1..ARRAY_LENGTH(list,2) LOOP
      IF LENGTH(row) = 0 THEN
        row = row || list[i][j];
      ELSE
        row = row || ',' || list[i][j];
      END IF;
    END LOOP;
    /* Exit outer loop. */
    RAISE NOTICE 'Row [%][%]', i, row;
  END LOOP;
END;
$$;

It prints:

NOTICE:  Row [1][1,2,3,4]
NOTICE:  Row [2][1,2,3,4]
NOTICE:  Row [3][1,2,3,4]
NOTICE:  Row [4][1,2,3,4]
NOTICE:  Row [5][1,2,3,4]
DO

Multidimensional arrays are unique to PostgreSQL but you can have nested lists of tables or varrays inside an Oracle database. Oracle also supports nested lists that are asynchronous.

As always, I hope this helps those trying sort out the syntax.

PL/pgSQL Array Listing

without comments

Somebody asked me how to navigate a collection in PostgreSQL’s PL/pgSQL and whether they supported table and varray data types, like Oracle’s PL/SQL. The most important thing to correct was that PostgreSQL supports only array types.

The only example that I found with a google search used a FOREACH-loop, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DO
$$
DECLARE
  /* An array of integers. */
  list  int[] = array[1,2,3,4,5];
  /* Define a local variable for array members. */
  i     int;
BEGIN
  /* Loop through the integers. */
  FOREACH i IN ARRAY list LOOP
    RAISE NOTICE '[%]', i;
  END LOOP;
END;
$$;

It prints:

NOTICE:  [1]
NOTICE:  [2]
NOTICE:  [3]
NOTICE:  [4]
NOTICE:  [5]

As I suspected the student didn’t want to use a FOREACH-loop. The student wanted to use a for-loop, which was much closer to the Oracle PL/SQL syntax with which they were most familiar. That example is:

1
2
3
4
5
6
7
8
9
10
11
12
DO
$$
DECLARE
  /* An array of integers. */
  list  int[] = array[1,2,3,4,5];
BEGIN
  /* Loop through the integers. */
  FOR i IN 1..5 LOOP
    RAISE NOTICE '[%]', list[i];
  END LOOP;
END;
$$;

However, it’s bad form to use a literal for the upper number in a range for-loop, and you should use the CARDINALITY function in PostgreSQL because there is no collection API, like Oracle’s COUNT method. There is an ARRAY_LENGTH function but it’s really only necessary when you use a multidimensional array.

The modified code is:

1
2
3
4
5
6
7
8
9
10
11
12
DO
$$
DECLARE
  -- An array of integers.
  list  int[] = array[1,2,3,4,5];
BEGIN
  /* Loop through the integers. */
  FOR i IN 1..CARDINALITY(list) LOOP
    RAISE NOTICE '[%]', list[i];
  END LOOP;
END;
$$;

If you use the ARRAY_LENGTH function, line #8 would look like:

7
8
  /* Loop through the integers, and determines the length of the first dimension. */
  FOR i IN 1..ARRAY_LENGTH(list,1) LOOP

As always, I hope this helps those looking for a clear solution to basic activities.

Written by maclochlainn

April 27th, 2022 at 1:21 am

PostgreSQL+PowerShell

with one comment

This post explains and demonstrates how to install, configure, and use the psqlODBC (32-bit) and psqlODBC (64-bit) libraries to connect your Microsoft PowerShell programs to a locally installed PostgreSQL 14 database. It relies on you previously installing and configuring a PostgreSQL 14 database. This post is a step-by-step guide to installing PostgreSQL 14 on Windows 10, and this post shows you how to configure the PostgreSQL 14 database.

If you didn’t follow the instructions to get the psqlODBC libraries in the installation blog post, you will need to get those libraries, as qualified by Microsoft with the PostgreSQL Stack Builder.

You can launch PostgreSQL Stack Builder after the install by clicking on Start -> PostgreSQL -> Stack Builder. Choose to enable Stack Builder to change your system and install the psqlODBC libraries. After you’ve installed the psqlODBC library, use Windows search field to find the ODBC Data Sources dialog and run it as administrator.

There are six steps to setup, test, and save your ODBC Data Source Name (DSN). You can click on the images on the right to launch them in a more readable format or simply read the instructions.

PostgreSQL ODBC Setup Steps

  1. The Microsoft DSN (Data Source Name) dialog automatically elects the User DSN tab. Click on the System DSN tab.

  1. The view under the System DSN is exactly like the User DSN tab. Click the Add button to start the workflow.

  1. The Create New Data Source dialog requires you select the PostgreSQL ODBC Driver(UNICODE) option from the list and click the Finish button to proceed.

  1. The PostgreSQL Unicode ODBC Driver Setup dialog should complete the prompts as follows below and consistent with the PostgreSQL 14 Configuration blog. If you opt for localhost as the server value because you have a DCHP IP address, make sure you’ve configured your hosts file in the C:\Windows\System32\drivers\etc directory. You should enter the following two lines in the hosts file:

    127.0.0.1  localhost
    ::1        localhost

    These are the string values you should enter in the PostgreSQL Unicode ODBC Driver Setup dialog:

    Data Source: PostgreSQL35W
    Database:    videodb
    Server:      localhost
    User Name:   student
    Description: PostgreSQL
    SSL Mode:    disable
    Port:        5432
    Password:    student

    After you complete the entry, click the Test button.

  1. The Connection Test dialog should return a “Connection successful” message. Click the OK button to continue.

  1. The ODBC Data Source Administrator dialog should show the PostgreSQL35W System Data Source. Click the OK button to continue.

After you have created the System PostgreSQL ODBC Setup, it’s time to build a PowerShell Cmdlet (or, Commandlet). Some documentation and blog notes incorrectly suggest you need to write a connection string with a UID and password, like:

$ConnectionString = 'DSN=PostgreSQL35W;Uid=student;Pwd=student'

The UID and password is unnecessary in the connection string. As a rule, the UID and password are only necessary in the ODBC DSN, like:

$ConnectionString = 'DSN=PostgreSQL35W'

You can create a readcursor.ps1 Cmdlet like the following:

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
33
34
35
36
37
# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=PostgreSQL35W'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT current_database();";
 
  # Attempt to read SQL command.
  try {
    $Reader = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($Reader.Read()) {
      Write-Host "Current Database [" $Reader[0] "]"}
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $Reader.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

Line 14 assigns a SQL query that returns a single row with one column as the CommandText of a Command object. Line 22 reads the zero position of a row or record set with only one column.

You call the readcursor.ps1 Cmdlet with the following syntax:

powershell .\readcursor.ps1

It returns:

Current Database [ videodb ]

A more realistic way to write a query would return multiple rows with a set of two or more columns. The following program queries a table with multiple rows of two columns, but the program logic can manage any number of columns.

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=PostgreSQL35W'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT last_name, first_name FROM contact ORDER BY 1, 2";
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
        }
        # Write the output from the database.
        Write-Host $output
      }
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

You call the readcontact.ps1 Cmdlet with the following syntax:

powershell .\readcontact.ps1

It returns an ordered set of comma-separated values, like

Clinton, Goeffrey
Gretelz, Simon
Moss, Wendy
Royal, Elizabeth
Smith, Brian
Sweeney, Ian
Sweeney, Matthew
Sweeney, Meaghan
Vizquel, Doreen
Vizquel, Oscar
Winn, Brian
Winn, Randi

As always, I hope this helps those looking for a complete concrete example of how to make Microsoft Powershell connect and query results from a PostgreSQL database.

PostgreSQL Arrays

with one comment

If you’re wondering about this post, it shows the basic array of a set of integers and strings before showing you how to create nested tables of data in PostgreSQL. By the way, they’re not called nested tables in PostgreSQL, like they are in Oracle but perform like their Oracle cousins.

Let’s create a table with an auto-incrementing column and two arrays, one array of integers and another of strings:

-- Conditionally drop the demo table.
DROP TABLE IF EXISTS demo;
 
-- Create the test table.
CREATE TABLE demo
( demo_id     serial
, demo_number integer[5]
, demo_string varchar(5)[7]);

You can insert test values like this:

INSERT INTO demo
(demo_number, demo_string)
VALUES
( array[1,2,3,4,5]
, array['One','Two','Three','Four','Five','Six','Seven']);

Then, you can query them with this unnest function, like:

SELECT  unnest(demo_number) AS numbers
,       unnest(demo_string) AS strings
FROM    demo;

It returns:

 numbers | strings
---------+---------
 1       | One
 2       | Two
 3       | Three
 4       | Four
 5       | Five
         | Six
         | Seven
(7 rows)

You may note that the two arrays are asymmetrical. It only becomes an issue when you navigate the result in a PL/pgSQL cursor or imperative programming language, like Python.

Now, let’s do something more interesting like work with a composite user-defined type, like the player structure. You would create the composite user-defined type with this syntax:

-- Conditionally drop the player type.
DROP TYPE IF EXISTS player;
 
-- Create the player type.
CREATE TYPE player AS
( player_no        integer
, player_name      varchar(24)
, player_position  varchar(14)
, ab               integer
, r                integer
, h                integer
, bb               integer
, rbi              integer );

You can create a world_series table that include a players column that uses an array of player type, like

-- Conditionally drop the world_series table.
DROP TABLE IF EXISTS world_series;
 
-- Create the player type.
CREATE TABLE world_series
( world_series_id  serial
, team             varchar(24)
, players          player[30]
, game_no          integer
, year             integer );

If you’re familiar with the Oracle Database, you’d have to specify a tested table in the syntax. Fortunately, PostgreSQL doesn’t require that.

Insert two rows with the following statement:

INSERT INTO world_series
( team
, players
, game_no
, year )
VALUES
('San Francisco Giants'
, array[(24,'Willie Mayes','Center Fielder',5,0,1,0,0)::player
       ,(5,'Tom Haller','Catcher',4,1,2,0,2)::player]
, 4
, 1962 );

You can append to the array with the following syntax. A former student and I have a disagreement on whether this is shown in the PostgreSQL 8.15.4 Modifying Array documentation. I believe it can be inferred from the document and he doesn’t believe so. Anyway, here’s how you add an element to an existing array in a table with the UPDATE statement:

UPDATE world_series
SET    players = (SELECT array_append(players,(7,'Henry Kuenn','Right Fielder',3,0,0,1,0)::player) FROM world_series)
WHERE  team = 'San Francisco Giants'
AND    year = 1962
AND    game_no = 4;

Like Oracle’s nested tables, PostgreSQL’s arrays of composite user-defined types requires writing a PL/pgSQL function. I’ll try to add one of those shortly in another blog entry to show you how to edit and replace entries in stored arrays of composite user-defined types.

You can query the unnested rows and get a return set like a Python tuple with the following query:

SELECT unnest(players) AS player_list
FROM   world_series
WHERE  team = 'San Francisco Giants'
AND    year = 1962
AND    game_no = 4;

It returns the three rows from the players array:

                 player_list
----------------------------------------------
 (24,"Willie Mayes","Center Field",5,0,1,0,0)
 (5,"Tom Haller",Catcher,4,1,2,0,2)
 (7,"Henry Kuenn","Right Fielde",3,0,0,1,0)
(3 rows)

It returns the data set in entry-order. If we step outside of the standard 8.15 Arrays PostgreSQL Documentation, you can do much more with arrays (or nested tables). The balance of this example demonstrates some new syntax that helps you achieve constructive outcomes in PostgreSQL.

You can use a Common Table Expression (CTE) to get the columnar display of the player composite user-defined type. This type of solution is beyond the standard , like:

WITH list AS
 (SELECT unnest(players) AS row_result
  FROM   world_series
  WHERE  team = 'San Francisco Giants'
  AND    year = 1962
  AND    game_no = 4)
SELECT  (row_result).player_name
,       (row_result).player_no
,       (row_result).player_position
FROM     list;

If you’re unfamiliar with accessing composite user-defined types, I wrote a post on that 7 years ago. You can find the older blog entry PostgreSQL Composites on my blog.

It returns only the three requested columns of the player composite user-defined type:

 player_name  | player_no | player_position
--------------+-----------+-----------------
 Willie Mayes |        24 | Center Fielder
 Tom Haller   |         5 | Catcher
 Henry Kuenn  |         7 | Right Fielder
(3 rows)

You should note that the data is presented in an entry-ordered manner when unnested alone in the SELECT-list. That behavior changes when the SELECT-list includes non-array data.

The easiest way to display data from the non-array and array columns is to list them inside the SELECT-list of the CTE, like:

WITH list AS
 (SELECT game_no AS game
  ,      year
  ,      unnest(players) AS row_result
  FROM   world_series
  WHERE  team = 'San Francisco Giants'
  AND    year = 1962
  AND    game_no = 4)
SELECT   game
,        year 
,       (row_result).player_name
,       (row_result).player_no
,       (row_result).player_position
FROM     list;

It returns an ordered set of unnested rows when you include non-array columns, like:

 game | year | player_name  | player_no | player_position
------+------+--------------+-----------+-----------------
    4 | 1962 | Henry Kuenn  |         7 | Right Fielder
    4 | 1962 | Tom Haller   |         5 | Catcher
    4 | 1962 | Willie Mayes |        24 | Center Fielder
(3 rows)

While you can join the world_series table to the unnested array rows (returned as a derived table, its a bad idea. The mechanics to do it require you to return the primary key column in the same SELECT-list of the CTE. Then, you join the CTE list to the world_series table by using the world_series_id primary key.

However, there is no advantage to an inner join approach and it imposes unnecessary processing on the database server. The odd rationale that I heard when I noticed somebody was using a CTE to base-table join was: “That’s necessary so they could use column aliases for the non-array columns.” That’s not true because you can use the aliases inside the CTE, as shown above when game is an alias to the game_no column.

As always, I hope this helps those looking to solve a problem in PostgreSQL.

Drop Overloaded Routine

without comments

In October 2019, I wrote a post with anonymous block programs to drop tables, sequences, routines, and triggers. Two weeks later, I wrote another post to drop all overloaded routines. However, I recognized the other day that I should have written a function that let you target which function or procedure you want to drop.

The older code only let you drop all of your functions or procedures. That was overkill when you’re working on new functions or procedures.

This post provides a utility for those writing functions and procedures in a public schema of any database in a PostgreSQL installation. It is designed to drop functions or procedures from the public schema.

The code follows below:

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
CREATE OR REPLACE
  FUNCTION drop_routine( IN pv_routine_name    VARCHAR(64)
                       , IN pv_routine_type    VARCHAR(64))
  RETURNS INTEGER AS
  $$
  DECLARE
    /* Declare the current catalog. */
    lv_local_catalog  VARCHAR(64) := current_database();
 
    /* Declare return type variable. */
    lv_retval  INTEGER := 1;
 
    /* Manage data dictionary case mechanics:
       ======================================
	     routine_name is always in lowercase.
	     routine_type is always in uppercase. */
    lv_routine_name  VARCHAR(64) := LOWER(pv_routine_name);
    lv_routine_type  VARCHAR(64) := UPPER(pv_routine_type);
 
    /* Declare an indefinite length string for SQL statement. */
    sql  VARCHAR;
 
    /* Declare variables to manage cursor return values. */
    row  RECORD;
    arg  VARCHAR;
 
    /* Declare parameter list. */
    list VARCHAR;
 
    /* Declare a routine cursor. */
    routine_cursor CURSOR( cv_routine_name  VARCHAR
                         , cv_routine_type  VARCHAR ) FOR
      SELECT r.routine_name
      ,      r.specific_name
      ,      r.routine_type
      FROM   information_schema.routines r
      WHERE  r.specific_catalog = current_database()
      AND    r.routine_schema = 'public'
      AND    r.routine_type = cv_routine_type
      AND    r.routine_name = cv_routine_name;
 
    /* Declare a parameter cursor. */
    parameter_cursor CURSOR( cv_specific_name  VARCHAR ) FOR
      SELECT args.data_type
      FROM   information_schema.parameters args
      WHERE  args.specific_catalog = current_database()
      AND    args.specific_schema = 'public'
      AND    args.specific_name = cv_specific_name;
 
  BEGIN
    /* Open the cursor. */
    OPEN routine_cursor(lv_routine_name, lv_routine_type);
    <<row_loop>>
    LOOP
      /* Fetch table names. */
      FETCH routine_cursor INTO row;
 
      /* Exit when no more records are found. */
      EXIT row_loop WHEN NOT FOUND;
 
      /* Initialize parameter list. */
      list := '(';
 
      /* Open the parameter cursor. */
      OPEN parameter_cursor(row.specific_name::varchar);
      <<parameter_loop>>
      LOOP
        FETCH parameter_cursor INTO arg;
 
        /* Exit the parameter loop. */
        EXIT parameter_loop WHEN NOT FOUND;
 
        /* Add parameter and delimit more than one parameter with a comma. */
        IF LENGTH(list) > 1 THEN
          list := CONCAT(list,',',arg);
        ELSE
          list := CONCAT(list,arg);
        END IF;
      END LOOP;
 
      /* Close the parameter list. */
      list := CONCAT(list,')');
 
      /* Close the parameter cursor. */
      CLOSE parameter_cursor;
 
      /* Concatenate together a DDL to drop the table with prejudice. */
      sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name||list;
 
      /* Execute the DDL statement. */
      EXECUTE sql;
 
      /* Assign success flag of 0. */
      lv_retval := 0;
 
    END LOOP;
 
    /* Close the routine_cursor. */
    CLOSE routine_cursor;
 
    /* Return the output text variable. */
    RETURN lv_retval;
  END
  $$ LANGUAGE plpgsql;

If you now create a series of hello overloaded functions, like:

CREATE OR REPLACE
  FUNCTION hello()
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT 'Hello World!' INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;
 
CREATE OR REPLACE
  FUNCTION hello(whom text)
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT CONCAT('Hello ',whom,'!') INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;
 
CREATE OR REPLACE
  FUNCTION hello(id int, whom text)
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT CONCAT('[',id,'] Hello ',whom,'!') INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;

After you create the overloaded functions, you can query their status from the information_schema.routines table in the data dictionary:

SELECT routine_name
,      specific_name
,      routine_type
FROM   information_schema.routines
WHERE  specific_catalog = current_setting('videodb.catalog_name')
AND    routine_schema = 'public'
AND    routine_name = 'hello';

Which shows you the three versions of the hello function:

 routine_name | specific_name | routine_type
--------------+---------------+--------------
 hello        | hello_18100   | FUNCTION
 hello        | hello_18101   | FUNCTION
 hello        | hello_18102   | FUNCTION
(3 rows)

You can drop all versions of the hello functions by calling the drop_routine function:

SELECT CASE
         WHEN drop_routine('hello','function') = 0
         THEN 'Success'
         ELSE 'Failure'
       END AS drop_routine;

It returns the following:

 drop_routine
--------------
 Success
(1 row)

As always, I hope this helps those looking for how to routinely test new functions and procedures.

Written by maclochlainn

March 6th, 2022 at 11:53 pm

Install Python on Windows

without comments

A number of my students want to run the databases and development environments on Windows rather than Linux. Some of the students have various problems configuring a virtual machine or Docker environment on their laptops.

Installing Python on Windows is quite straightforward. You simply open a Windows Command Line Interface (CLI) with Administrator privileges and type python at the command line. It will launch a dialog that lets you download and install Python.

After the installation, you are returned to the Windows CLI where you can type python to launch the Python IDE. You’ll learn that it’s an older version when it opens.

Python 3.10.2 (tags/v3.10.2:a58ebcc, Jan 17 2022, 14:12:15) [MSC v.1929 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>>

After installing Python, I started to install drivers with the Python package-management utility – PIP. I loaded the psycopg2 driver first and discovered that Windows installed an older version of PIP, as qualified when I installed the Python library:

pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.9.3-cp310-cp310-win_amd64.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 1.6 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.9.3
WARNING: You are using pip version 21.2.4; however, version 22.0.3 is available.
You should consider upgrading via the 'C:\Users\mclaughlinm\AppData\Local\Microsoft\WindowsApps\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\python.exe -m pip install --upgrade pip' command.

So, I immediately installed the new version of PIP and discovered that I have to manually reset the %PATH% environment variable.

python -m pip install --upgrade pip
Requirement already satisfied: pip in c:\program files\windowsapps\pythonsoftwarefoundation.python.3.10_3.10.752.0_x64__qbz5n2kfra8p0\lib\site-packages (21.2.4)
Collecting pip
  Downloading pip-22.0.3-py3-none-any.whl (2.1 MB)
     |████████████████████████████████| 2.1 MB 656 kB/s
Installing collected packages: pip
  WARNING: The scripts pip.exe, pip3.10.exe and pip3.exe are installed in 'C:\Users\mclaughlinm\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\Scripts' which is not on PATH.
  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed pip-22.0.3

The default location for the PIP.exe and PIP3.exe are found in the:

C:\Users\mclaughlinm\AppData\Local\Microsoft\WindowsApps

Rather than put the newer directory in the System Environment %PATH% variable, I created a batch file that lets me set it only when I need it.

SET PATH=C:\Users\mclaughlinm\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\Scripts;%PATH%

After installing the psycopg2 driver, I tested the new_hire.sql and new_hire.py files from this earlier Linux post successfully. I hope this helps those looking to work with Python on Windows.

Written by maclochlainn

March 2nd, 2022 at 11:48 pm