Archive for the ‘Linux’ Category
PostgreSQL Table Function
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.
PL/pgSQL List to Struct
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
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.
PL/SQL List to Struct
Every now and then, I get questions from folks about how to tune in-memory elements of their PL/SQL programs. This blog post address one of those core issues that some PL/SQL programmers avoid.
Specifically, it addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/SQL it’s a table of scalar or object types). Oracle lingo hides the similarity by calling either an Attribute Definition Type (ADT) or User-Defined Type (UDT). The difference in the Oracle space is that an ADT deals with a type defined in DBMS_STANDARD package, which is more or less like a primitive type in Java.
Oracle does this for two reasons:
- They handle lists of standard types in a difference C++ class than they do UDT types.
- They rigidly adhere to Interface Definition Language (IDL) principles.
The cast_strings function converts a list of strings into a record data structure. It lets the list of strings have either a densely or sparsely populated list of values, and it calls the verify_date function to identify a DATE data type and regular expressions to identify numbers and strings.
You need to build a UDT object type and lists of both ADT and UDT data types.
/* Create a table of strings. */ CREATE OR REPLACE TYPE tre AS TABLE OF VARCHAR2(20); / /* Create a structure of a date, number, and string. */ CREATE OR REPLACE TYPE struct IS OBJECT ( xdate DATE , xnumber NUMBER , xstring VARCHAR2(20)); / /* Create a table of tre type. */ CREATE OR REPLACE TYPE structs IS TABLE OF struct; / |
The cast_strings function is defined below:
CREATE OR REPLACE FUNCTION cast_strings ( pv_list TRE ) RETURN struct IS /* Declare a UDT and initialize an empty struct variable. */ lv_retval STRUCT := struct( xdate => NULL , xnumber => NULL , xstring => NULL); BEGIN /* Loop through list of values to find only the numbers. */ FOR i IN 1..pv_list.LAST LOOP /* Ensure that a sparsely populated list can't fail. */ IF pv_list.EXISTS(i) THEN /* Order if number evaluation before string evaluation. */ CASE WHEN lv_retval.xnumber IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN lv_retval.xnumber := pv_list(i); WHEN verify_date(pv_list(i)) THEN IF lv_retval.xdate IS NULL THEN lv_retval.xdate := pv_list(i); ELSE lv_retval.xdate := NULL; END IF; WHEN lv_retval.xstring IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:alnum:]]*$') THEN lv_retval.xstring := pv_list(i); ELSE NULL; END CASE; END IF; END LOOP; /* Print the results. */ RETURN lv_retval; END; / |
There are three test cases for this function:
- The first use-case checks whether the input parameter is a sparsely or densely populated list:
DECLARE /* Declare an input variable of three or more elements. */ lv_list TRE := tre('Berlin','25','09-May-1945','45'); /* Declare a variable to hold the compound type values. */ lv_struct STRUCT; BEGIN /* Make the set sparsely populated. */ lv_list.DELETE(2); /* Test the cast_strings function. */ lv_struct := cast_strings(lv_list); /* Print the values of the compound variable. */ dbms_output.put_line(CHR(10)); dbms_output.put_line('xstring ['||lv_struct.xstring||']'); dbms_output.put_line('xdate ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']'); dbms_output.put_line('xnumber ['||lv_struct.xnumber||']'); END; /
It should return:
xstring [Berlin] xdate [09-MAY-1945] xnumber [45]
The program defines two numbers and deletes the first number, which is why it prints the second number.
- The second use-case checks with a list of only one element:
SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate , xnumber , xstring FROM TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))));
It should return:
XDATE XNUMBER XSTRING -------------------- ---------- -------------------- 25-NOV-1945 25 catch22
The program returns a structure with values converted into their appropriate data type.
- The third use-case checks with a list of two elements:
SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate , xnumber , xstring FROM TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945')) ,cast_strings(tre('31-APR-2017','1918','areodromes'))));
It should return:
XDATE XNUMBER XSTRING -------------------- ---------- -------------------- 25-NOV-1945 25 catch22 1918 areodromes
The program defines calls the cast_strings with a valid set of values and an invalid set of values. The invalid set of values contains a bad date in the set of values.
As always, I hope this helps those looking for how to solve this type of problem.
PL/SQL CASE Not Found
I was working on some test cases for my students and changing the behavior of a verify_date function that I wrote years ago to validate and returns valid dates when they’re passed as strings. The original program returned today’s date when the date was invalid.
The new function returns a BOOLEAN value of false by default and true when the string validates as a date. Unfortunately, I introduced a mistake that didn’t use to exist in Oracle 11g, which was the version when I wrote the original function.
The test cases in Oracle 21c raises the following error when an invalid date is passed to the CASE statement by the cast_strings function that calls the new verify_date function:
FROM TABLE(structs(cast_strings(tre('31-APR-2017','1917','dirk')))) * ERROR AT line 2: ORA-06592: CASE NOT found WHILE executing CASE statement ORA-06512: AT "C##STUDENT.VERIFY_DATE", line 30 ORA-06512: AT "C##STUDENT.CAST_STRINGS", line 18 |
As you can see, the test case uses ’31-APR-2017′ as an incorrect date to verify the use-case. The error occurred because the ELSE clause in the CASE statement wasn’t provided. Previously, the ELSE clause was optional and setting the lv_retval return variable to FALSE in the DECLARE block made it unnecessary.
The fixed code follows:
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 | CREATE OR REPLACE FUNCTION verify_date ( pv_date_in VARCHAR2) RETURN BOOLEAN IS /* Local variable to ensure case-insensitive comparison. */ lv_date_in VARCHAR2(11); /* Local return variable. */ lv_date BOOLEAN := FALSE; BEGIN /* Convert string input to uppercase month. */ lv_date_in := UPPER(pv_date_in); /* Check for a DD-MON-RR or DD-MON-YYYY string. */ IF REGEXP_LIKE(lv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN /* Case statement checks for 28 or 29, 30, or 31 day month. */ CASE /* Valid 31 day month date value. */ WHEN SUBSTR(lv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN lv_date := TRUE; /* Valid 30 day month date value. */ WHEN SUBSTR(lv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN lv_date := TRUE; /* Valid 28 or 29 day month date value. */ WHEN SUBSTR(lv_date_in,4,3) = 'FEB' THEN /* Verify 2-digit or 4-digit year. */ IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN lv_date := TRUE; ELSE /* Not a leap year. */ IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN lv_date := TRUE; END IF; END IF; ELSE NULL; END CASE; END IF; /* Return date. */ RETURN lv_date; EXCEPTION WHEN VALUE_ERROR THEN RETURN lv_date; END; / |
The new ELSE clause in on lines 31 and 32, and the converted function works. I also added a local lv_date_in variable to hold an uppercase version of an input string to: ensure a case-insensitive comparison of the month value, and avoid a having to pass the input as an IN OUT mode parameter. Typically, I leave off exception handlers because mistyping or copying for newer programmers becomes easier, but in this case I added an exception handler for strings that are larger than 11-characters.
As always, I hope this helps those looking for a solution to a coding problem.
Fedora for macOS ARM64
I’m always updating VMs, and I was gratified to notice that there’s a Fedora arm64 ISO. If you’re interested in it, you can download the Live Workstation from here or the Fedora Server from here.
Unfortunately, I only have macOS running on i7 and i9 Intel Processors. It would be great to hear back how it goes for somebody one of the new Apple M1 chip.
I typically install the workstation version because it meets my needs to run MySQL and other native Linux development tools. However, the server version is also available. Fedora is a wonderful option, as a small footprint for testing things on my MacBookPro.
Java Gregorian Date
One of my students asked for an example of how to work with a Gregorian date and timezones in Java. I dug out an old example file from when I taught Java at Regis University.
The code follows:
/* || Program name: MyGregorian.java || Created by: Michael McLaughlin || Creation date: 10/07/02 || History: || ---------------------------------------------------------------------- || Date Author Purpose || -------- ---------------------- --------------------------------- || dd/mm/yy {Name} {Brief statement of change.} || ---------------------------------------------------------------------- || Execution method: Static class demonstrating timezone setting. || Program purpose: Designed as a stand alone program. */ // Class imports. import java.util.Calendar; import java.util.GregorianCalendar; import java.util.TimeZone; // Define MyGregorian class. public class MyGregorian { // Testing static main() method. public static void main(String args[]) { // Set an initial variable. String initial = ""; System.out.println("======================================================"); System.out.println("Value of [user.timezone]: [" + (initial = (null != System.getProperty("user.timezone")) ? "Unset" : System.getProperty("user.timezone")) + "]"); System.out.println("======================================================"); GregorianCalendar gc = (GregorianCalendar) Calendar.getInstance(); System.out.println("Calendar Date: [" + gc.getTime() + "]"); gc.add(GregorianCalendar.MONTH,1); System.out.println("Calendar Date: [" + gc.getTime() + "]"); System.out.println("======================================================"); System.out.println("Value of [user.timezone]: [" + System.getProperty("user.timezone") + "]"); System.out.println("Value of [user.timezone]: [" + System.setProperty("user.timezone","") + "]"); gc.add(GregorianCalendar.MONTH,1); System.out.println("Calendar Date: [" + gc.getTime() + "]"); System.out.println("======================================================"); Calendar c = Calendar.getInstance(); System.out.println("Calendar Date: [" + c.getTime() + "]"); // Move the date ahead one month, hour and minute. c.add(Calendar.MONTH,1); System.out.println("Calendar Date: [" + c.getTime() + "]"); System.out.println("======================================================"); } // End of testing static main() method. } // End of MyGregorian class. |
It prints to console:
====================================================== Value of [user.timezone]: [Unset] ====================================================== Calendar Date: [Thu May 05 23:43:42 MDT 2022] Calendar Date: [Sun Jun 05 23:43:42 MDT 2022] ====================================================== Value of [user.timezone]: [America/Denver] Value of [user.timezone]: [America/Denver] Calendar Date: [Tue Jul 05 23:43:42 MDT 2022] ====================================================== Calendar Date: [Thu May 05 23:43:42 MDT 2022] Calendar Date: [Sun Jun 05 23:43:42 MDT 2022] ====================================================== |
As always, I hope this helps those who need to see and example to work with Gregorian dates.
Bash Debug Function
My students working in Linux would have a series of labs to negotiate and I’d have them log the activities of their Oracle SQL scripts. Many of them would suffer quite a bit because they didn’t know how to find the errors in the log files.
I wrote this SQL function for them to put in their .bashrc files. It searches all the .txt files for errors and organizes them by log file, line number, and descriptive error message.
errors () { label="File Name:Line Number:Error Code"; list=`ls ./*.$1 | wc -l`; if [[ ${list} -eq 1 ]]; then echo ${label}; echo "----------------------------------------"; filename=`ls *.txt`; echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-`; else if [[ ${list} -gt 1 ]]; then echo ${label}; echo "----------------------------------------"; find . -type f | grep --color=auto -in *.txt -e ora\- -e pls\- -e sp2\-; fi; fi } |
I hope it helps others now too.
MySQL RegExp Default
We had an interesting set of questions regarding the REGEXP comparison operator in MySQL today in both sections of Database Design and Development. They wanted to know the default behavior.
For example, we built a little movie table so that we didn’t change their default sakila example database. The movie table was like this:
CREATE TABLE movie ( movie_id int unsigned primary key auto_increment , movie_title varchar(60)) auto_increment=1001; |
Then, I inserted the following rows:
INSERT INTO movie ( movie_title ) VALUES ('The King and I') ,('I') ,('The I Inside') ,('I am Legend'); |
Querying all results with this query:
SELECT * FROM movie; |
It returns the following results:
+----------+----------------+ | movie_id | movie_title | +----------+----------------+ | 1001 | The King and I | | 1002 | I | | 1003 | The I Inside | | 1004 | I am Legend | +----------+----------------+ 4 rows in set (0.00 sec) |
The following REGEXP returns all the rows because it looks for a case insensitive “I” anywhere in the string.
SELECT movie_title FROM movie WHERE movie_title REGEXP 'I'; |
The implicit regular expression is actually:
WHERE movie_title REGEXP '^.*I.*$'; |
It looks for zero-to-many of any character before and after the “I“. You can get any string beginning with an “I” with the “^I“; and any string ending with an “I” with the “I$“. Interestingly, the “I.+$” should only match strings with one or more characters after the “I“, but it returns:
+----------------+ | movie_title | +----------------+ | The King and I | | The I Inside | | I am Legend | +----------------+ 3 rows in set (0.00 sec) |
This caught me by surprise because I was lazy. As pointed out in the comment, it only appears to substitute a “.*“, or zero-to-many evaluation for the “.+” because it’s a case-insensitive search. There’s another lowercase “i” in the “The King and I” and that means the regular expression returns true because that “i” has one-or-more following characters. If we convert it to a case-sensitive comparison with the keyword binary, it works as expected because it ignores the lowercase “i“.
WHERE binary movie_title REGEXP '^.*I.*$'; |
This builds on my 10-year old post on Regular Expressions. As always, I hope these notes helps others discovering features and behaviors of the MySQL database, and Bill thanks for catching my error.
Record Type Arrays
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.
Subscribe via RSS