MacLochlainns Weblog

These are the instructions for installing AlmaLinux 9 on MacOS Intel with VMware. It’s broken into three parts: Configuration, Installation, and Setup.

You should download the current or desired older version of AlmaLinux from the website. It’s a good idea to install it in a source directory or folder.

You need to launch VMware, click the File menu option and then the New menu option before you can configure, install, and setup AlmaLinux. It will show you the following dialog.

Use the Finder to drag the AlmaLinux-9-latest-x86_64-dvd.iso file on to the Install from disc or image to begin the configuration process.

Configuration

1. The first thing you need to do is configure the VMware container. You click on the wrench icon in the menu bar that lets you edit the hardware settings for this virtual machine. This opens the general settings dialog.

2. The General System Settings dialog lets you will configure the isolation properties for “drag and drop” and “copy and paste”, and define the network adapter.

3. Click on the Isolation menu option from the General System Settings dialog, and enable “Drag adn Drop” and “Copy and Paste” checkboxes. Then, return to the General System Settings dialog/li>

4. Click on the Network Adapter menu option from the General System Settings dialog, and click the “Autodetect” checkbox. Then, return to the General System Settings dialog.

Installation

1. The first the install asks you to do is type an I for install or T for test. Generally, you can skip the test if this is a copied file rather than DVD.

2. Click the Tab key to configure the target installation or Enter key to skip the automatic boot delay.

3. Choose the installation language and click the Continue button.

4. The Installation Summary dialog lets you fix anything with a warning message orange triangle. Fix the Root Password first by clicking on the text.

5. Enter the Root Password twice, click the Lock root account checkbox to unselect it, and click the Allow root SSH login with password checkbox to enable it.

6. Click the Done button to complete setting the root password.

7. After fixing the root password, click on the Installation Destination item to change the default partition.

8. Click the Done button to accept the initial size of the VM partition.

9. Click the Software Selection element to add software components to the default installation.

10. Click the checkboxes for the following additional software:
    • Debugging Tools
    • Performance Tools
    • Remote Desktop Clients
    • Remote Management for Linux
    • Legacy UNIX Compatibility
    • Console Internet Tools
    • Development Tools
    • .NET Development
    • Graphical Administration Tools
    • System Tools

    Click the Done button to accept the added software elements.

11. Click the Begin Installation button to begin the installation of AlmaLinux.

12. The Installing Progress dialog will show a progress bar for several minutes.

13. The Installing Progress dialog eventually completes, and you click the Reboot System button to complete the installation.

Setup

1. The Welcome to AlmaLinux page begins the set up of the operating systems.

2. Click the Slide to disable location services.

3. After clicking the Slide location services are disabled.

4. The Online Account dialog lets you connect your email, online calendar, contacts, documents, and photos. You click the Skip button to avoid setting up any of the online accounts.

5. The About You dialog lets you enter user account with sudoer privileges.

6. The About You dialog enters a title case user’s name and a lowercase user’s name.

7. The Password dialog lets you enter a case sensitive password.

8. The Password dialog displays the entry of the passwords as dots.

9. The Setup Complete dialog leaves you to click the Start Using AlmaLinux button to complete the setup.

10. The blank image page displays until you click on it.

11. The Welcome to AlmaLinux dialog invites you to take a tour or decline it. Click the No Thanks button to decline the tour.

12. After declining the tour, it displays the core AlmaLinux screen.

13. Rebooting the system is necessary to complete the installation. Click on the circle icon on the right to begin the process to restart or shutdown the operating system. Click on the Power Off / Log Out dropdown to continue.

14. Click the Restart… menu option to restart the operating system.

15. Click the Restart… menu option to confirm the restart of the operating system.

16. Click the Student icon to get prompted for a password.

17. Enter the Student password to connect to the AlmaLinux operating system.

18. This displays the standard working interface for the AlmaLinux operating system.

After installing AlmaLinux in a VMware VM on my MacBook Pro (Intel Chip), and updating the packages with the following command:

sudo dnf upgrade --refresh -y

MySQL was first on my installation and configuration list. Here are the commands to install and configure it on AlmaLinux.

Install the MySQL Server packages and dependents:

sudo dnf install mysql mysql-server -y

Install the MySQL service utilities with the initscripts package, using the following command:

sudo yum install -y initscripts

Start the MySQL daemon with the following command:

sudo service mysqld start

Connect and verify the root user can connect to the database. At this point, you’ve not set the root user’s password and should use the following syntax:

mysql -uroot

It should connect and display:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30 Source distribution
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Exit MySQL and run the following command:

mysql_secure_installation

It’ll run you through the following prompts, which you may change to suit your installation. My choices are a trivial student database isolated inside a VM.

Securing the MySQL server deployment.
 
Connecting to MySQL using a blank password.
 
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
 
Press y|Y for Yes, any other key for No: n
Please set the password for root here.
 
New password: 
 
Re-enter new password: 
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
 
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
 
 
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
 
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
 
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
 
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.
 
 - Removing privileges on test database...
Success.
 
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
 
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
 
All done!

The next step requires setting up the sample sakila and studentdb database. The syntax has changed from prior releases. Here are the new three steps:

1. Grant the root user the privilege to grant to others, which root does not have by default. You use the following syntax as the MySQL root user:

   mysql> GRANT ALL ON *.* TO 'root'@'localhost';

   mysql> GRANT ALL ON *.* TO 'root'@'localhost';

2. Download the sakila database, which you can download from this site. Click on the sakila database’s TGZ download.

   

   When you download the sakila zip file it creates a sakila-db folder in the /home/student/Downloads directory. Copy the sakila-db folder into the /home/student/Data/sakila directory. Then, change to the /home/student/Data/sakila/sakila-db directory, connect to mysql as the root user, and run the following command:

   mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-schema.sql mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-data.sql

   mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-schema.sql mysql> SOURCE /home/student/Data/sakila/sakila-db/sakila-data.sql

3. Create the studentdb database with the following command as the MySQL root user:

   mysql> CREATE DATABASE studentdb;

   mysql> CREATE DATABASE studentdb;

4. Create the user with a clear English password and grant the user student full privileges on the sakila and studentdb databases:

   mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost'; mysql> GRANT ALL ON sakila.* TO 'student'@'localhost';

   mysql> CREATE USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'student'; mysql> GRANT ALL ON studentdb.* TO 'student'@'localhost'; mysql> GRANT ALL ON sakila.* TO 'student'@'localhost';

You can now connect to a sandboxed sakila database with the student user’s credentials, like:

mysql -ustudent -p -Dsakila

or, you can now connect to a sandboxed studentdb database with the student user’s credentials, like:

mysql -ustudent -p -Dstudentdb

As always, I hope code and step complete instructions help others get things done more quickly.

So, I wrote an updated example of my grandma and tweetie_bird for my students. It demonstrates overloading with the smallest parameter lists possible across a transaction of two tables. It also shows how one version of the procedure can call another version of the procedure.

The tables are created with the following:

/* Conditionally drop grandma table and grandma_s sequence. */
BEGIN
  FOR i IN (SELECT object_name
            ,      object_type
            FROM   user_objects
            WHERE  object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP
    IF i.object_type = 'TABLE' THEN
      /* Use the cascade constraints to drop the dependent constraint. */
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the table. */
CREATE TABLE GRANDMA
( grandma_id     NUMBER       CONSTRAINT grandma_nn1 NOT NULL
, grandma_house  VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL
, created_by     NUMBER       CONSTRAINT grandma_nn3 NOT NULL
, CONSTRAINT grandma_pk       PRIMARY KEY (grandma_id)
);
 
/* Create the sequence. */
CREATE SEQUENCE grandma_seq;
 
/* Conditionally drop a table and sequence. */
BEGIN
  FOR i IN (SELECT object_name
            ,      object_type
            FROM   user_objects
            WHERE  object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create the table with primary and foreign key out-of-line constraints. */
CREATE TABLE TWEETIE_BIRD
( tweetie_bird_id     NUMBER        CONSTRAINT tweetie_bird_nn1 NOT NULL
, tweetie_bird_house  VARCHAR2(30)  CONSTRAINT tweetie_bird_nn2 NOT NULL
, grandma_id          NUMBER        CONSTRAINT tweetie_bird_nn3 NOT NULL
, created_by          NUMBER        CONSTRAINT tweetie_bird_nn4 NOT NULL
, CONSTRAINT tweetie_bird_pk        PRIMARY KEY (tweetie_bird_id)
, CONSTRAINT tweetie_bird_fk        FOREIGN KEY (grandma_id)
  REFERENCES GRANDMA (GRANDMA_ID)
);
 
/* Create sequence. */
CREATE SEQUENCE tweetie_bird_seq;

The sylvester package specification holds the two overloaded procedures, like:

CREATE OR REPLACE
  PACKAGE sylvester IS
 
  /* Three variable length strings. */
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_name    VARCHAR2  );
 
  /* Two variable length strings and a number. */  
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_id      NUMBER   );
 
END sylvester;
/

The sylvester package implements two warner_brother procedures. One takes the system user’s ID and the other takes the system user’s name. The procedure that accepts the system user name queries the system_user table with the system_user_name to get the system_user_id column and then calls the other version of itself. This demonstrates how you only write logic once when overloading and let one version call the other with the added information.

Here’s the sylvester package body code:

CREATE OR REPLACE
  PACKAGE BODY sylvester IS
 
  /* Procedure warner_brother with user name. */
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_id      NUMBER  ) IS
 
    /* Declare a local variable for an existing grandma_id. */
    lv_grandma_id   NUMBER;
 
    FUNCTION get_grandma_id
    ( pv_grandma_house  VARCHAR2 ) RETURN NUMBER IS
 
      /* Initialized local return variable. */
      lv_retval  NUMBER := 0;  -- Default value is 0.
 
      /* A cursor that lookups up a grandma's ID by their name. */
      CURSOR find_grandma_id
      ( cv_grandma_house  VARCHAR2 ) IS
        SELECT grandma_id
        FROM   grandma
        WHERE  grandma_house = cv_grandma_house;
 
    BEGIN   
      /* Assign a grandma_id as the return value when a row exists. */
      FOR i IN find_grandma_id(pv_grandma_house) LOOP
        lv_retval := i.grandma_id;
      END LOOP;
 
      /* Return 0 when no row found and the grandma_id when a row is found. */
      RETURN lv_retval;
    END get_grandma_id;
 
  BEGIN
    /* Set the savepoint. */
    SAVEPOINT starting;
 
    /*
     *  Identify whether a member account exists and assign it's value
     *  to a local variable.
     */
    lv_grandma_id := get_grandma_id(pv_grandma_house);
 
    /*
     *  Conditionally insert a new member account into the member table
     *  only when a member account does not exist.
     */
    IF lv_grandma_id = 0 THEN
 
      /* Insert grandma. */
      INSERT INTO grandma
      ( grandma_id
      , grandma_house
      , created_by )
      VALUES
      ( grandma_seq.NEXTVAL
      , pv_grandma_house
      , pv_system_user_id  );
 
      /* Assign grandma_seq.currval to local variable. */
      lv_grandma_id := grandma_seq.CURRVAL;
 
    END IF;
 
    /* Insert tweetie bird. */
    INSERT INTO tweetie_bird
    ( tweetie_bird_id
    , tweetie_bird_house 
    , grandma_id
    , created_by )
    VALUES
    ( tweetie_bird_seq.NEXTVAL
    , pv_tweetie_bird_house
    , lv_grandma_id
    , pv_system_user_id );
 
    /* If the program gets here, both insert statements work. Commit it. */
    COMMIT;
 
  EXCEPTION
    /* When anything is broken do this. */
    WHEN OTHERS THEN
      /* Until any partial results. */
      ROLLBACK TO starting;
  END;
 
  PROCEDURE warner_brother
  ( pv_grandma_house       VARCHAR2
  , pv_tweetie_bird_house  VARCHAR2
  , pv_system_user_name    VARCHAR2  ) IS
 
    /* Define a local variable. */
	lv_system_user_id  NUMBER := 0;
 
    FUNCTION get_system_user_id
    ( pv_system_user_name  VARCHAR2 ) RETURN NUMBER IS
 
      /* Initialized local return variable. */
      lv_retval  NUMBER := 0;  -- Default value is 0.
 
      /* A cursor that lookups up a grandma's ID by their name. */
      CURSOR find_system_user_id
      ( cv_system_user_id  VARCHAR2 ) IS
        SELECT system_user_id
        FROM   system_user
        WHERE  system_user_name = pv_system_user_name;
 
    BEGIN   
      /* Assign a grandma_id as the return value when a row exists. */
      FOR i IN find_system_user_id(pv_system_user_name) LOOP
        lv_retval := i.system_user_id;
      END LOOP;
 
      /* Return 0 when no row found and the grandma_id when a row is found. */
      RETURN lv_retval;
    END get_system_user_id;
 
  BEGIN
 
    /* Convert a system_user_name to system_user_id. */
	lv_system_user_id := get_system_user_id(pv_system_user_name);
 
	/* Call the warner_brother procedure. */
	warner_brother
    ( pv_grandma_house      => pv_grandma_house
    , pv_tweetie_bird_house => pv_tweetie_bird_house
    , pv_system_user_id     => lv_system_user_id  );
 
  EXCEPTION
    /* When anything is broken do this. */
    WHEN OTHERS THEN
      /* Until any partial results. */
      ROLLBACK TO starting;
  END;  
 
END sylvester;
/

The following anonymous block test case works with the code:

BEGIN
  sylvester.warner_brother( pv_grandma_house      => 'Blue House'
                          , pv_tweetie_bird_house => 'Cage'
				          , pv_system_user_name   => 'DBA 3' );
  sylvester.warner_brother( pv_grandma_house      => 'Blue House'
                          , pv_tweetie_bird_house => 'Tree House'
				          , pv_system_user_id     =>  4 );
END;
/

You can now query the results with this SQL*PLus formatting and query:

/* Query results from warner_brother procedure. */
COL grandma_id          FORMAT 9999999  HEADING "Grandma|ID #"
COL grandma_house       FORMAT A14      HEADING "Grandma House"
COL created_by          FORMAT 9999999  HEADING "Created|By"
COL tweetie_bird_id     FORMAT 9999999  HEADING "Tweetie|Bird ID"
COL tweetie_bird_house  FORMAT A18      HEADING "Tweetie Bird House"
SELECT *
FROM   grandma g INNER JOIN tweetie_bird tb
ON     g.grandma_id = tb.grandma_id;

You should see the following data:

 Grandma                 Created  Tweetie                     Grandma  Created
    ID # Grandma House        By  Bird ID Tweetie Bird House     ID #       By
-------- -------------- -------- -------- ------------------ -------- --------
       1 Blue House            3        1 Cage                      1        3
       1 Blue House            3        2 Tree House                1        4

As always, I hope complete code samples help solve real problems.

While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.

The error is caused by this import statement:

const mysql = require('mysql')

The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.

internal/modules/cjs/loader.js:638
    throw err;
    ^
 
Error: Cannot find module 'mysql'
    at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15)
    at Function.Module._load (internal/modules/cjs/loader.js:562:25)
    at Module.require (internal/modules/cjs/loader.js:692:17)
    at require (internal/modules/cjs/helpers.js:25:18)
    at Object.<anonymous> (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    at Module._compile (internal/modules/cjs/loader.js:778:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)

I explained they could fix the problem with the following two Node.js Package Manager (NPM) commands:

npm init --y 
npm install --save mysql

The student was able to retest the code with success. The issue was simply that the Node.js couldn’t find the NPM MySQL module.

MySQL Partitioned Tables

Learning Outcomes

• Learn about List Partitioning.
• Learn about Range Partitioning.
• Learn about Columns Partitioning.
• Learn about Hash Partitioning.
• Learn about Key Partitioning.
• Learn about Subpartitioning.

Lesson Material

MySQL supports partitioning of tables. It supports range, list, hash, and key partitioning. Range partitioning lets you partition based on column values that fall within given ranges. List partitioning lets you partition based on columns matching one of a set of discrete values. Hash partitioning lets you partition based on the return value from a user-defined expression (the result from a stored SQL/PSM function). Key partitioning performs like hash partitioning, but it lets a user select one or more columns from the set of columns in a table; a hash manages the selection process for you. A hash is a method of organizing keys to types of data, and hashes speed access to read and change data in tables.

Each of the following subsections discusses one of the supported forms of partitioning in MySQL. Naturally, there are differences between other databases and MySQL’s implementation.

List Partitioning

A MySQL list partition works by identifying a column that contains an integer value, the franchise_number in the following example. Partitioning clauses follow the list of columns and constraints and require a partitioning key to be in the primary key or indexed.

The following list partition works with literal numeric values. MySQL uses the IN keyword for list partitions. Note that there’s no primary key designated and an index is on the auto-incrementing surrogate key column. A complete example is provided to avoid confusion on how to index the partitioning key:

CREATE TABLE franchise
( franchise_id     INT UNSIGNED AUTO_INCREMENT
, franchise_number INT UNSIGNED
, franchise_name   VARCHAR(20)
, city             VARCHAR(20)
, state            VARCHAR(20)
, index idx (franchise_id))
PARTITION BY LIST(franchise_number)
( PARTITION offshore VALUES IN (49,50)
, PARTITION west VALUES IN (34,45,48)
, PARTITION desert VALUES IN (46,47)
, PARTITION rockies VALUES IN (38,41,42,44));

The inclusion of a PRIMARY KEY constraint on the franchise_id column would trigger an ERROR 1503 when the partitioning key isn’t the primary key. The reason for the error message is that a primary key implicitly creates a unique index, and that index would conflict with the partitioning by list instruction. The use of a non-unique idx index on the franchise_id column is required when you want to partition on a non-primary key column.

Range Partitioning

Range partitioning works only with an integer value or an expression that resolves to an integer against the primary key column. The limitation of the integer drives the necessity of choosing an integer column for range partitioning. You can’t define a range-partitioned table with a PRIMARY KEY constraint unless the primary key becomes your partitioning key, like
the one below.

CREATE TABLE ordering
( ordering_id     INT UNSIGNED AUTO_INCREMENT
, item_id         INT UNSIGNED
, rental_amount   DECIMAL(15,2)
, rental_date     DATE
, index idx (ordering_id))
PARTITION BY RANGE(item_id)
( PARTITION jan2011 VALUES LESS THAN (10000) , PARTITION feb2011 VALUES LESS THAN (20000) , PARTITION mar2011 VALUES LESS THAN (30000));

Range partitioning is best suited to large tables that you want to break into smaller pieces based on the integer column. You can also use stored functions that return integers as the partitioning key instead of the numeric literals shown. Few other options are available in MySQL.

Columns Partitioning

Columns partitioning is a new variant of range and list partitioning. It is included in MySQL 5.5 and forward. Both range and list partitioning work on an integer-based column (using TINYINT, SMALLINT, MEDIUMINT, INT [alias INTEGER], and BIGINT). Columns partitioning extends those models by expanding the possible data types for the partitioning column to include CHAR, VARCHAR, BINARY, and VARBINARY string data types, and DATE, DATETIME, or TIMESTAMP data types. You still can’t use other number data types such as DECIMAL and FLOAT. The TIMESTAMP data type is also available only in range partitions with the caveat that you use a UNIX_TIMESTAMP function, according to MySQL Bug 42849.

Hash Partitioning

Hash partitions ensure an even distribution of rows across a predetermined number of partitions. It is probably the easiest way to partition a table quickly to test the result of partitioning on a large table. You should base hash partitions on a surrogate or natural primary key.

The following provides a modified example of the ordering table:

CREATE TABLE ordering
( ordering_id       INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, item_id           INT UNSIGNED
, rental_amount     DECIMAL(15,2)
, rental_date       DATE)
PARTITION BY HASH(ordering_id) PARTITIONS 8;

This is the partitioning type that benefits from a PRIMARY KEY constraint because it automatically creates a unique index that can be used by the hash. A non-unique index such as the list partitioning example doesn’t work for a hash partition.

Key Partitioning

Key partitioning is valuable because you can partition on columns that aren’t integers. It performs along the line of hash partitioning, except the MySQL Server uses its own hashing expression.

CREATE TABLE orders_list
( order_list_id     INT UNSIGNED AUTO_INCREMENT
, customer_surname  VARCHAR(30)
, store_id          INT UNSIGNED
, salesperson_id    INT UNSIGNED
, order_date        DATE
, index idx (order_list_id))
PARTITION BY KEY (order_date) PARTITIONS 8;

This is the only alternative when you want to partition by date ranges. Like the hash partition, it’s easy to deploy. The only consideration is the number of slices that you want to make of the data in the table.

Subpartitioning

The concept of subpartitioning is also known as composite partitioning. You can subpartition range or list partitions with a hash, linear hash, or linear key.

A slight change to the previously created ordering table is required to demonstrate composite partitioning: we’ll add a store_id column to the table definition. The following is an example of a range partition subpartitioned by a hash:

CREATE TABLE ordering
INT UNSIGNED
INT UNSIGNED
DATE
 ( ordering_id     INT UNSIGNED AUTO_INCREMENT
, item_id          INT UNSIGNED
, store_id         INT UNSIGNED
, rental_amount    DECIMAL(15,2)
, rental_date      DATE
, index idx (ordering_id))
PARTITION BY RANGE(item_id)
  SUBPARTITION BY HASH(store_id) SUBPARTITIONS 4
( PARTITION jan2011 VALUES LESS THAN (10000)
, PARTITION feb2011 VALUES LESS THAN (20000)
, PARTITION mar2011 VALUES LESS THAN (30000));

Composite partitioning is non-trivial and might require some experimentation to achieve optimal results. Plan on making a few tests of different scenarios before you deploy a solution.

Oracle Partitioned Tables

Learning Outcomes

• Learn about List Partitioning.
• Learn about Range Partitioning.
• Learn about Hash Partitioning.
• Learn about Composite Partitioning.

Lesson Material

Partitioning is the process of breaking up a data source into a series of data sources. Partitioned tables are faster to access and transact against. Partitioning data becomes necessary as the amount of data grows in any table. It speeds the search to find rows and insert, update, or delete rows.

Oracle Database 21c supports four types of table partitioning: list, range, hash, and composite partitioning.

List Partitioning

A list partition works by identifying a column that contains a value, such as a STATE column in an ADDRESS table. Partitioning clauses follow the list of columns and constraints.

A list partition could use a STATE column, like the following (the complete example is avoided to conserve space, and the three dots represent the balance of partitions not shown):

CREATE TABLE franchise
( franchise_id    NUMBER CONSTRAINT pk_franchise PRIMARY KEY
, franchise_name  VARCHAR(20)
, city            VARCHAR(20)
, state           VARCHAR(20))
PARTITION BY LIST(state)
( PARTITION offshore VALUES('Alaska', 'Hawaii')
, PARTITION west VALUES('California', 'Oregon', 'Washington')
, PARTITION desert VALUES ('Arizona','New Mexico')
, PARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
, ... );

This can be used with other values such as ZIP codes with great effect, but the maintenance of list partitioning can be considered costly. Cost occurs when the list of values changes over time. Infrequent change means low cost, while frequent change means high costs. In the latter case, you should consider other partitioning strategies. Although an Oracle database supports partitioning on a variable-length string, MySQL performs list partitioning only on integer columns.

Range Partitioning

Range partitioning is very helpful on any column that contains a continuous metric, such as dates or time. It works by stating a minimum set that is less than a certain value, and then a group of sets of higher values until you reach the top most set of values. This type of partition helps you improve performance by letting you search ranges rather than complete data sets. Range partitioning is also available in MySQL.

A range example based on dates could look like this:

PARTITION BY RANGE(rental_date)
( PARTITION rental_jan2011
  VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY')
, PARTITION rental_feb2011
  VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY')
, PARTITION rental_mar2011
  VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY')
, ... );

The problem with this type of partitioning, however, is that the new months require constant management. Many North American businesses simply add partitions for all months in the year as an annual maintenance task during the holidays in November or December. Companies that opt for bigger range increments reap search and access benefits from range partitioning, while minimizing ongoing maintenance expenses.

Hash Partitioning

Hash partitioning is much easier to implement than list or range partitioning. Many DBAs favor it because it avoids the manual maintenance of list and range partitioning. Oracle Database 21c documentation recommends that you implement a hash for the following reasons:

• There is no concrete knowledge about how much data maps to a partitioning range.
• The sizes of partitions are unknown at the outset and difficult to balance as data is added to the database.
• A range partition might cluster data in an ineffective way.

This next statement creates eight partitions and stores them respectively in one of the eight tablespaces. The hash partition manages nodes and attempts to balance the distribution of rows across the nodes.

PARTITION BY HASH(store)
PARTITIONS 8
STORE IN (tablespace1, tablespace2, tablespace3, tablespace4
         ,tablespace5, tablespace6, tablespace7, tablespace8);

As you can imagine the maintenance for this type of partitioning is low. Some DBAs choose this method to get an initial sizing before adopting a list or range partitioning plan. Maximizing the physical resources of the machine ultimately rests with the DBAs who manage the system. Developers need to stand ready to assist DBAs with analysis and syntax support.

Composite Partitioning

Composite partitioning requires a partition and subpartition. The composites are combinations of two types of partitioning—typically, list and range partitioning, or range and hash composite partitioning. Which of these you should choose depends on a few considerations. List and range composite partitioning is done for historical information and is well suited for data warehouses. This method lets you partition on unordered or unrelated column values.

A composite partition like this uses the range as the partition and the list as the subpartition, like the following:

PARTITION BY RANGE (rental_date)
 SUBPARTITION BY LIST (state)
 (PARTITION FQ1_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY'))
  (SUBPARTITION offshore VALUES('Alaska', 'Hawaii')
  , SUBPARTITION west VALUES('California', 'Oregon', 'Washington')
  , SUBPARTITION desert VALUES ('Arizona','New Mexico')
  , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
  , ... )
,(PARTITION FQ2_1999 VALUES LESS THAN (TO_DATE('1-APR-2011','DD-MON-YYYY'))
  (SUBPARTITION offshore VALUES('Alaska', 'Hawaii')
  , SUBPARTITION west VALUES('California', 'Oregon', 'Washington')
  , SUBPARTITION desert VALUES ('Arizona','New Mexico')
  , SUBPARTITION rockies VALUES ('Colorado', 'Idaho', 'Montana', 'Wyoming')
  , ... )
, ... )

Range and hash composite partitioning is done for historical information when you also need to stripe data. Striping is the process of creating an attribute in a table that acts as a natural subtype or separator of data. Users typically view data sets of one subtype, which means organizing the data by stripes (subtypes) can speed access based on user access patterns.

Range is typically the partition and the hash is the subpartition in this composite partitioning schema. The syntax for this type of partition is shown next:

PARTITION BY RANGE (rental_date)
 SUBPARTITION BY HASH(store)
  SUBPARTITIONS 8 STORE IN (tablespace1, tablespace2, tablespace3
                           ,tablespace4, tablespace5, tablespace6
                           ,tablespace7, tablespace8)
   ( PARTITION rental_jan2011
     VALUES LESS THAN TO_DATE('31-JAN-11','DD-MON-YY')
   , PARTITION rental_feb2011
     VALUES LESS THAN TO_DATE('28-FEB-11','DD-MON-YY')
   , PARTITION rental_mar2011
     VALUES LESS THAN TO_DATE('31-MAR-11','DD-MON-YY')
   , ... )

It is interesting when somebody remembers a presentation from 10 years ago. They asked if it was possible in PL/pgSQL to write an autonomous procedure to log data when calling a table view function. The answer is two fold. PL/pgSQL doesn’t support autonomous functions or procedures like the Oracle database but it doesn’t need to because unless you invoke a transaction it auto commits writes.

Logging table functions are important for security auditing and compliance management against laws, like SOX, HIPAA, and FERPA. All too many systems lack the basic ability to audit who queries records without raising an error and blocking the access. That means the bad actor or actress gains the ability to probe the system for weaknesses before determining an attack vector. It’s often better to capture the unauthorized access and take direct action to protect both the the data and systems.

While the example lets an unauthorized person access the information in the first version of the student_query, it blocks access by reporting no rows returned in the latter. Both versions of the query log the data and thereby collect the evidence necessary to act against the hack.

This blog post shows you how to write it and test it. Follow the following steps:

1. Create the necessary tables and data to work with a logging PL/pgSQL table view function:

   /* Conditionally drop and create table. */ DROP TABLE IF EXISTS student; CREATE TABLE student ( student_id SERIAL , first_name VARCHAR(20) , last_name VARCHAR(20) , hogwarts_house VARCHAR(10));   /* Conditionally drop and create table. */ DROP TABLE IF EXISTS logger; CREATE TABLE logger ( logger_id SERIAL , app_user VARCHAR(30) , queried_student VARCHAR(30) , query_time TIMESTAMP );   /* Insert one record into table. */ INSERT INTO student ( first_name, last_name, hogwarts_house ) VALUES ( 'Harry', 'Potter', 'Gryffindor' ) ,( 'Hermione', 'Granger', 'Gryffindor' ) ,( 'Ronald', 'Weasily', 'Gryffindor' ) ,( 'Draco', 'Malfoy', 'Slytherin' ) ,( 'Vincent', 'Crabbe', 'Slytherin' ) ,( 'Susan', 'Bones', 'Hufflepuff' ) ,( 'Hannah', 'Abbott', 'Hufflepuff' ) ,( 'Luna', 'Lovegood', 'Ravenclaw' ) ,( 'Cho', 'Chang', 'Ravenclaw' ) ,( 'Gilderoy', 'Lockhart', 'Ravenclaw' );

   /* Conditionally drop and create table. */ DROP TABLE IF EXISTS student; CREATE TABLE student ( student_id SERIAL , first_name VARCHAR(20) , last_name VARCHAR(20) , hogwarts_house VARCHAR(10)); /* Conditionally drop and create table. */ DROP TABLE IF EXISTS logger; CREATE TABLE logger ( logger_id SERIAL , app_user VARCHAR(30) , queried_student VARCHAR(30) , query_time TIMESTAMP ); /* Insert one record into table. */ INSERT INTO student ( first_name, last_name, hogwarts_house ) VALUES ( 'Harry', 'Potter', 'Gryffindor' ) ,( 'Hermione', 'Granger', 'Gryffindor' ) ,( 'Ronald', 'Weasily', 'Gryffindor' ) ,( 'Draco', 'Malfoy', 'Slytherin' ) ,( 'Vincent', 'Crabbe', 'Slytherin' ) ,( 'Susan', 'Bones', 'Hufflepuff' ) ,( 'Hannah', 'Abbott', 'Hufflepuff' ) ,( 'Luna', 'Lovegood', 'Ravenclaw' ) ,( 'Cho', 'Chang', 'Ravenclaw' ) ,( 'Gilderoy', 'Lockhart', 'Ravenclaw' );

2. While not necessary if you’re very familiar with PL/pgSQL, it may be helpful to review:
   • The SET command that lets you assign a value to a session-level variable, which you can later use in a PL/pgSQL block.
   • The SELECT-INTO statement in a DO-block.

   Here’s a test script that demonstrates both:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

   /* Set a session-level variable. */ SET credential.app_user = 'Draco Malfoy';   /* Secure the value from a session-level variable. */ SELECT current_setting('credential.app_user');   /* DO $$ DECLARE input VARCHAR(30) := 'Hermione'; output VARCHAR(30); BEGIN /* Sample for partial name construction of full name. */ SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name INTO output FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||input||'%';   /* Show result of local assignment via a query. */ RAISE NOTICE '[%][%]', current_setting('credential.app_user'), output; END; $$;

   /* Set a session-level variable. */ SET credential.app_user = 'Draco Malfoy'; /* Secure the value from a session-level variable. */ SELECT current_setting('credential.app_user'); /* DO $$ DECLARE input VARCHAR(30) := 'Hermione'; output VARCHAR(30); BEGIN /* Sample for partial name construction of full name. */ SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name INTO output FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||input||'%'; /* Show result of local assignment via a query. */ RAISE NOTICE '[%][%]', current_setting('credential.app_user'), output; END; $$;

   There’s an important parsing trick to this sample program. It uses the LIKE operator rather than the SIMILAR TO operator because the parser fails to recognize the SIMILAR TO operator.

   The DO-block returns the following output:

   NOTICE: [Draco Malfoy][Hermione Granger]

   NOTICE: [Draco Malfoy][Hermione Granger]

3. This creates the student_query logging table function, which takes a partial portion of a students first and last name to return the student information. While the example only returns the name and the Hogwarts House it lays a foundation for a more complete solution.

   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

   CREATE OR REPLACE FUNCTION student_query (partial_name VARCHAR) RETURNS TABLE ( first_naem VARCHAR(20) , last_name VARCHAR(20) , hogwarts_house VARCHAR(10) ) AS $$ DECLARE queried VARCHAR; by_whome VARCHAR; BEGIN /* Query separately because embedding in insert statement fails. */ SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name FROM student s INTO queried WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';   /* Log the query with the credentials of the user. */ INSERT INTO logger ( app_user , queried_student , query_time ) VALUES ( current_setting('credential.app_user') , queried , NOW());   /* Return the result set without disclosing the query was recorded. */ RETURN QUERY SELECT s.first_name , s.last_name , s.hogwarts_house FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'; END; $$ LANGUAGE plpgsql;

   CREATE OR REPLACE FUNCTION student_query (partial_name VARCHAR) RETURNS TABLE ( first_naem VARCHAR(20) , last_name VARCHAR(20) , hogwarts_house VARCHAR(10) ) AS $$ DECLARE queried VARCHAR; by_whome VARCHAR; BEGIN /* Query separately because embedding in insert statement fails. */ SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name FROM student s INTO queried WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'; /* Log the query with the credentials of the user. */ INSERT INTO logger ( app_user , queried_student , query_time ) VALUES ( current_setting('credential.app_user') , queried , NOW()); /* Return the result set without disclosing the query was recorded. */ RETURN QUERY SELECT s.first_name , s.last_name , s.hogwarts_house FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'; END; $$ LANGUAGE plpgsql;

4. You can test the function by calling it, like this:

   SELECT * FROM student_query('Hermione');

   SELECT * FROM student_query('Hermione');

   It displays:

   first_naem | last_name | hogwarts_house ------------+-----------+---------------- Hermione | Granger | Gryffindor (1 row)

   first_naem | last_name | hogwarts_house ------------+-----------+---------------- Hermione | Granger | Gryffindor (1 row)

   You can check the logging table and discover who looked up another student’s records.

   SELECT * FROM logger;

   SELECT * FROM logger;

   It displays:

   logger_id | app_user | queried_student | query_time -----------+--------------+------------------+---------------------------- 1 | Draco Malfoy | Hermione Granger | 2022-05-29 22:51:50.398987 (1 row)

   logger_id | app_user | queried_student | query_time -----------+--------------+------------------+---------------------------- 1 | Draco Malfoy | Hermione Granger | 2022-05-29 22:51:50.398987 (1 row)

5. Assuming you’ve built an authorized_user function that returns a Boolean, you can add a call to it in the WHERE clause. For simplicity, let’s implement the function to deny all users, like:

   1 2 3 4 5 6 7 8 9 10

   CREATE OR REPLACE FUNCTION authorized_user (user_name VARCHAR) RETURNS BOOLEAN AS $$ DECLARE lv_retval BOOLEAN := FALSE; BEGIN RETURN lv_retval; END; $$ LANGUAGE plpgsql;

   CREATE OR REPLACE FUNCTION authorized_user (user_name VARCHAR) RETURNS BOOLEAN AS $$ DECLARE lv_retval BOOLEAN := FALSE; BEGIN RETURN lv_retval; END; $$ LANGUAGE plpgsql;

   You can now replace the query on lines 28 through 32 with the new one below. The added clause on line 33 denies access to unauthorized users because there aren’t any.

   28 29 30 31 32 33

   SELECT s.first_name , s.last_name , s.hogwarts_house FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%' AND authorized_user(current_setting('credential.app_user'));

   SELECT s.first_name , s.last_name , s.hogwarts_house FROM student s WHERE CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%' AND authorized_user(current_setting('credential.app_user'));

   While it returns:

   first_naem | last_name | hogwarts_house ------------+-----------+---------------- (0 rows)

   first_naem | last_name | hogwarts_house ------------+-----------+---------------- (0 rows)

   The logger table shows two entries. One for the query that returned a value and one for the version that didn’t.

   logger_id | app_user | queried_student | query_time -----------+--------------+------------------+---------------------------- 1 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:39.82063 2 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:40.736945 (2 rows)

   logger_id | app_user | queried_student | query_time -----------+--------------+------------------+---------------------------- 1 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:39.82063 2 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:40.736945 (2 rows)

   In both cases the bad actor Draco Malfoy’s unauthorized access is captured and he was denied any information without alerting him to the security precaution in a logging table function.

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

An Oracle example was requested as a comparison against the quick tutorial I wrote on how to do this in PostgreSQL’s PL/pgSQL. Unfortunately, there are many more moving parts to deliver this type of solution in Oracle’s PL/SQL.

The functions is same and simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German. The additional moving parts are the required User-Defined Types (UDTs); one is a record structure and the other is a list (or Oracle parlance table).

The drops are unconditional and as such will trigger errors the first time they’re run but including PL/SQL blocks to make them conditional would have made the code much larger. It’s already larger because Oracle doesn’t support comma-delimited lists in the VALUES clause.

I’ll stage this with the same conquistador table used in the last post. Then, connect to the psql shell and run the following script file:

/* Drop the conquistador table. */
DROP TABLE conquistador;
 
/* Create the conquistador table. */
CREATE TABLE conquistador
( conquistador_id   NUMBER
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30)
, lang              VARCHAR(2));
 
/* Drop the conquistador sequence. */
DROP SEQUENCE conquistador_seq;
 
/* Create the conquistador_seq with a 1001 start value. */
CREATE SEQUENCE conquistador_seq START WITH 1001;
 
/* Insert 9 rows into the table. */
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Juan de Fuca','Ioánnis Fokás','Greek','el');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES 
(conquistador_seq.NEXTVAL,'Nicolás de Federmán','Nikolaus Federmann','German','de');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Sebastián Caboto','Sebastiano Caboto','Venetian','it');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Jorge de la Espira','Georg von Speyer','German','de');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Wenceslao Linck','Wenceslaus Linck','Bohemian','cs');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Fernando Consag','Ferdinand Konšcak','Croatian','sr');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Américo Vespucio','Amerigo Vespucci','Italian','it');
 
INSERT INTO conquistador
( conquistador_id, conquistador, actual_name, nationality, lang )
VALUES
(conquistador_seq.NEXTVAL,'Alejo García','Aleixo Garcia','Portuguese','pt');

While unnecessary in PL/pgSQL, you must create User-Defined Types (UDTs) to write a table function. You must also create a local procedure to avoid allocating memory before assigning values to the list. These are the UDTs required:

/* Drop the dependency before the dependent type. */
DROP TYPE conquistador_table;
DROP TYPE conquistador_struct;
 
/* Create the UDT for a record structure accessible in SQL. */
CREATE OR REPLACE
  TYPE conquistador_struct IS OBJECT
  ( conquistador      VARCHAR(30)
  , actual_name       VARCHAR(30)
  , nationality       VARCHAR(30));
/
 
/* Create a list of the UDT. */
CREATE OR REPLACE
  TYPE conquistador_table IS TABLE OF conquistador_struct;
/

Drop any existing function or procedure of the same name before you try to build it. Oracle’s OR REPLACE fails when you try to use it for a function when there is already a procedure using the same name, and vice versa.

/* Drop the function to avoid any conflict with a procedure of
   the same name. */
DROP FUNCTION getConquistador;

Now, you can build another script file to create the getConquistador function, like:

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

/* Create the function. */
CREATE OR REPLACE
  FUNCTION getConquistador
  (pv_lang IN VARCHAR) RETURN conquistador_table IS
 
  /* Declare a return variable. */
  lv_retval  CONQUISTADOR_TABLE := conquistador_table();
 
  /* Declare a dynamic cursor. */
  CURSOR get_conquistador
  ( cv_lang  VARCHAR2 ) IS
    SELECT c.conquistador
    ,      c.actual_name
    ,      c.nationality
    FROM   conquistador c
    WHERE  c.lang = cv_lang;
 
  /* Local procedure to add to the song. */
  PROCEDURE ADD
  ( pv_input  CONQUISTADOR_STRUCT ) IS
  BEGIN
    lv_retval.EXTEND;
    lv_retval(lv_retval.COUNT) := pv_input;
  END ADD;
 
BEGIN
  /* Read through the cursor and assign to the UDT table. */
  FOR i IN get_conquistador(pv_lang) LOOP
    add(conquistador_struct( i.conquistador
                           , i.actual_name
                           , i.nationality ));
  END LOOP;
 
  /* Return collection. */
  RETURN lv_retval;
END;
/

While there is some white space for readability, the Oracle version is basically twice as long as the PL/pgSQL version. It also requires you to add UDTs to the data dictionary to make it work. PL/pgSQL actually doesn’t let you add references to type definitions and requires you use enumerated descriptions with column definitions.

Then, you can test it with the following syntax. The TABLE function is required to convert the list to a SQL consumable result set:

COL conquistador  FORMAT A21
COL actual_name   FORMAT A21
COL nationality   FORMAT A12
SELECT * FROM TABLE(getConquistador('de'));

It will return the following:

CONQUISTADOR          ACTUAL_NAME           NATIONALITY
--------------------- --------------------- ------------
Nicolás de Federmán   Nikolaus Federmann    German
Jorge de la Espira    Georg von Speyer      German
 
2 rows selected.

As always, I hope this helps with a technique that’s useful.

A quick tutorial on how to write a PL/pgSQL Table function. The functions is simple. It returns the list of conquistadors that were originally German. It does that by filtering on the lang column in the table. For example, you use ‘de‘ for German.

I’ll stage this with the same conquistador table used in the last post. Don’t forget to use the chcp command to the Active Console Code Page to 4-byte Unicode before you run the script file, like:

chcp 65001

Then, connect to the psql shell and run the following script file:

/* Conditionally drop the conquistador table. */
DROP TABLE IF EXISTS conquistador;
 
/* Create the conquistador table. */
CREATE TABLE conquistador
( conquistador_id   SERIAL
, conquistador      VARCHAR(30)
, actual_name       VARCHAR(30)
, nationality       VARCHAR(30)
, lang              VARCHAR(2));
 
/* Insert some conquistadors into the table. */
INSERT INTO conquistador
( conquistador
, actual_name
, nationality
, lang )
VALUES
 ('Juan de Fuca','Ioánnis Fokás','Greek','el')
,('Nicolás de Federmán','Nikolaus Federmann','German','de')
,('Sebastián Caboto','Sebastiano Caboto','Venetian','it')
,('Jorge de la Espira','Georg von Speyer','German','de')
,('Eusebio Francisco Kino','Eusebius Franz Kühn','Italian','it')
,('Wenceslao Linck','Wenceslaus Linck','Bohemian','cs')
,('Fernando Consag','Ferdinand Konšcak','Croatian','sr')
,('Américo Vespucio','Amerigo Vespucci','Italian','it')
,('Alejo García','Aleixo Garcia','Portuguese','pt');

Now, you can build another script file to create the getConquistador function, like:

/* Drop the funciton conditionally. */
DROP FUNCTION IF EXISTS getConquistador;

Create the getConquistador function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE FUNCTION getConquistador (IN lang_in VARCHAR(2))
  RETURNS TABLE
    ( conquistador      VARCHAR(30)
    , actual_name       VARCHAR(30)
    , nationality       VARCHAR(30)) AS
$$
BEGIN
  RETURN QUERY
  SELECT c.conquistador
  ,      c.actual_name
  ,      c.nationality
  FROM   conquistador c
  WHERE  c.lang = lang_in;
END;
$$ LANGUAGE plpgsql;

Then, you can test it like:

SELECT * FROM getConquistador('de');

It will return the following:

     conquistador      |    actual_name     | nationality
-----------------------+--------------------+-------------
 Nicolás de Federmán   | Nikolaus Federmann | German
 Jorge de la Espira    | Georg von Speyer   | German
(2 rows)

As always, I hope this helps with a technique that’s useful.

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; $$;

  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]

  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;

  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)

  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.