MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for January, 2015

A PL/pgSQL Function

without comments

Somebody wanted to know how to write a basic PostgreSQL PL/pgSQL function that returned a full name whether or not the middle name was provided. That’s pretty simple. There are principally two ways to write that type of concatenation function. One uses formal parameter names and the other uses positional values in lieu of the formal parameter names.

The two ways enjoy two techniques (SQL language and PL/pgSQL language), which gives us four possible solutions. I’ve also provided a conditional drop statement for the full_name function. If you’re new to PostgreSQL the DROP statement might make you scratch your head because you’re wondering why you need to use the formal parameter list. The DROP statement needs the parameter list because PostgeSQL lets you overload schema/database functions and procedures.

The code is for a named parameter lists using the SQL language is:

DROP FUNCTION IF EXISTS full_name
( IN pv_first_name  text
, IN pv_middle_name text
, IN pv_full_name   text);
 
CREATE FUNCTION full_name
( IN  pv_first_name  text
, IN  pv_middle_name text
, IN  pv_last_name   text
, OUT pv_full_name   text) AS
   'SELECT  pv_first_name
    ||      CASE
              WHEN pv_middle_name IS NOT NULL THEN '' '' || pv_middle_name || '' ''
              ELSE '' ''
            END
    ||      pv_last_name'
    LANGUAGE SQL;

The code is for a positional parameter lists using the SQL language is:

DROP FUNCTION IF EXISTS full_name
( IN text
, IN text
, IN text);
 
CREATE FUNCTION full_name
( IN text
, IN text
, IN text
, OUT text) AS
   'SELECT  $1
    ||      CASE
              WHEN $2 IS NOT NULL THEN '' '' || $2 || '' ''
              ELSE '' ''
            END
    ||      $3'
    LANGUAGE SQL;

You would re-write the function in the PL/pgSQL language as follows (please note the named parameter list):

CREATE FUNCTION full_name
( IN  pv_first_name   text
, IN  pv_middle_name  text
, IN  pv_last_name    text) RETURNS text AS
$$
DECLARE
  lv_output text;
BEGIN
  IF pv_middle_name IS NULL THEN
    lv_output = CONCAT(pv_first_name, N' ', pv_last_name);
  ELSE
    lv_output = CONCAT(pv_first_name, N' ', pv_middle_name, N' ', pv_first_name);
  END IF;
  RETURN lv_output;
END
$$
LANGUAGE plpgsql IMMUTABLE;

You can test either version of the program with the following two queries from the pseudo table dual, which isn’t require in the SELECT statement:

SELECT full_name('Henry',NULL,'Pym') AS "Ant-Man"
UNION ALL
SELECT full_name('Henry','''Hank''','Pym') AS "Ant-Man";

It prints:

Ant-Man
text
----------------
Henry Pym
Henry 'Hank' Pym

As always, I hope this helps those looking for how to accomplish a concatenation function in PostgreSQL.

Written by maclochlainn

January 22nd, 2015 at 1:03 am

Convert to SQL Server?

with 2 comments

I’m always amazed at the questions that pop up for me. For example, how do you convert an Oracle script that creates my Video Store model to a Microsoft SQL Server script. It’s not very hard but there’s one big caveat, and that’s the fact that system_user is a reserved word. That means you can’t create the Access Control List (ACL) table with a system_user name. The alternative, would be to convert the system_user table name to database_user. That’s what I’ve done in this example.

It’s also important to note that this example uses Microsoft SQL Server’s sqlcmd in batch mode. Naturally, it presumes that you’ve created a student user with a trivial password of student, and a studentdb schema. Also, that you’ve granted privileges so everything works (if you need help on that check my earlier post on how to setup a studentdb schema).

The following is an example of conditionally dropping and then creating a system_user table in an Oracle schema. It uses a CASCADE CONSTRAINTS clause to eliminate dependencies with foreign key values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  table_name = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--  Create SYSTEM_USER table.
CREATE TABLE system_user
( system_user_id            NUMBER        CONSTRAINT system_user_pk PRIMARY KEY
, system_user_name          VARCHAR2(20)  CONSTRAINT system_user_nn1 NOT NULL
, system_user_group_id      NUMBER        CONSTRAINT system_user_nn2 NOT NULL
, system_user_type          NUMBER        CONSTRAINT system_user_nn3 NOT NULL
, first_name                VARCHAR2(20)
, middle_name               VARCHAR2(20)
, last_name                 VARCHAR2(20)
, created_by                NUMBER        CONSTRAINT system_user_nn4 NOT NULL
, creation_date             DATE          CONSTRAINT system_user_nn5 NOT NULL
, last_updated_by           NUMBER        CONSTRAINT system_user_nn6 NOT NULL
, last_update_date          DATE          CONSTRAINT system_user_nn7 NOT NULL
, CONSTRAINT system_user_fk1  FOREIGN KEY (created_by)
  REFERENCES system_user (system_user_id)
, CONSTRAINT system_user_fk2  FOREIGN KEY (last_updated_by)
  REFERENCES system_user (system_user_id));
 
-- Create SYSTEM_USER_S1 sequence with a start value of 1001.
CREATE SEQUENCE system_user_s1 START WITH 1001;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Conditionally drop the table and sequence.
BEGIN
  FOR i IN (SELECT NULL
            FROM   user_tables
            WHERE  table_name = 'COMMON_LOOKUP') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE common_lookup CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT NULL
            FROM   user_sequences
            WHERE  sequence_name = 'COMMON_LOOKUP_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE common_lookup_s1';
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--  Create COMMON_LOOKUP table.
CREATE TABLE common_lookup
( common_lookup_id            NUMBER
, common_lookup_context       VARCHAR2(30) CONSTRAINT nn_clookup_1 NOT NULL
, common_lookup_type          VARCHAR2(30) CONSTRAINT nn_clookup_2 NOT NULL
, common_lookup_meaning       VARCHAR2(30) CONSTRAINT nn_clookup_3 NOT NULL
, created_by                  NUMBER       CONSTRAINT nn_clookup_4 NOT NULL
, creation_date               DATE         CONSTRAINT nn_clookup_5 NOT NULL
, last_updated_by             NUMBER       CONSTRAINT nn_clookup_6 NOT NULL
, last_update_date            DATE         CONSTRAINT nn_clookup_7 NOT NULL
, CONSTRAINT pk_c_lookup_1    PRIMARY KEY(common_lookup_id)
, CONSTRAINT fk_c_lookup_1    FOREIGN KEY(created_by) REFERENCES system_user(system_user_id)
, CONSTRAINT fk_c_lookup_2    FOREIGN KEY(last_updated_by) REFERENCES system_user(system_user_id));
 
-- Create a non-unique index on a single column.
CREATE INDEX common_lookup_n1
  ON common_lookup(common_lookup_context);
 
-- Create a unique index based on two columns.
CREATE UNIQUE INDEX common_lookup_u2
  ON common_lookup(common_lookup_context,common_lookup_type);
 
-- Create COMMON_LOOKUP_S1 sequence with a start value of 1001.
CREATE SEQUENCE common_lookup_s1 START WITH 1001;

You can do the same thing for a database_user table in Microsoft SQL Server with the following syntax. Unfortunately, there isn’t a CASCADE CONSTRAINTS clause that we can append in Microsoft SQL Server. The script uses a dynamic SQL statement with a Common Table Expression (CTE) to generate a list of ALTER statements that drop foreign key constraints in the schema.

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
/* Drop all foreign keys. */
USE studentdb;
 
/* Create a session variable to hold a command list. */
SELECT 'Create a session variable.' AS "Statement";
DECLARE @sql NVARCHAR(MAX) = N'';
 
/* Generate the command list to drop foreign key constraints. */
SELECT 'Generate dynamic SQL statements.' AS "Statement";
;WITH x AS
(SELECT N'ALTER TABLE ' +
          OBJECT_SCHEMA_NAME(parent_object_id) + N'.' +
          OBJECT_NAME(parent_object_id) + N' ' +
        N'DROP CONSTRAINT ' + name + N';' AS sqlstmt
 FROM     sys.foreign_keys)
SELECT @sql += sqlstmt FROM x;
 
/* Call the dynamically generated statements. */
SELECT 'Execute dynamic SQL statements.' AS "Statement";
EXEC sp_executesql @sql;
 
/* Conditionally drop tables. */
SELECT 'Conditionally drop studentdb.common_lookup table.' AS "Statement";
IF OBJECT_ID('studentdb.database_user','U') IS NOT NULL
  DROP TABLE studentdb.database_user;
 
/* Create a table with self-referencing foreign key constraints. */
SELECT 'Create studentdb.common_lookup table.' AS "Statement";
CREATE TABLE studentdb.database_user
( database_user_id        INT          NOT NULL IDENTITY(1,1) CONSTRAINT database_user_pk PRIMARY KEY
, database_user_name      VARCHAR(20)  NOT NULL
, database_user_group_id  INT          NOT NULL
, database_user_type      INT          NOT NULL
, first_name              VARCHAR(20)
, middle_name             VARCHAR(20)
, last_name               VARCHAR(20)
, created_by              INT          NOT NULL
, creation_date           DATE         NOT NULL
, last_updated_by         INT          NOT NULL
, last_update_date        DATE         NOT NULL
, CONSTRAINT database_user_fk1 FOREIGN KEY (created_by)
  REFERENCES studentdb.database_user (database_user_id)
, CONSTRAINT database_user_fk2 FOREIGN KEY (created_by)
  REFERENCES studentdb.database_user (database_user_id));
 
/* Conditionally drop common_lookup table. */
SELECT 'Conditionally drop studentdb.common_lookup table.' AS "Statement";
IF OBJECT_ID('studentdb.common_lookup','U') IS NOT NULL
  DROP TABLE studentdb.common_lookup;
 
/* Create a table with external referencing foreign key constraints. */  
SELECT 'Create studentdb.common_lookup table.' AS "Statement";
CREATE TABLE studentdb.common_lookup
( common_lookup_id            INT          NOT NULL  IDENTITY(1,1) CONSTRAINT common_lookup_pk PRIMARY KEY
, common_lookup_context       VARCHAR(30)  CONSTRAINT nn_clookup_1 NOT NULL
, common_lookup_type          VARCHAR(30)  CONSTRAINT nn_clookup_2 NOT NULL
, common_lookup_meaning       VARCHAR(30)  CONSTRAINT nn_clookup_3 NOT NULL
, created_by                  INT          CONSTRAINT nn_clookup_4 NOT NULL
, creation_date               DATE         CONSTRAINT nn_clookup_5 NOT NULL
, last_updated_by             INT          CONSTRAINT nn_clookup_6 NOT NULL
, last_update_date            DATE         CONSTRAINT nn_clookup_7 NOT NULL
, CONSTRAINT common_lookup_fk1 FOREIGN KEY(created_by)
  REFERENCES studentdb.database_user (database_user_id)
, CONSTRAINT common_lookup_fk2 FOREIGN KEY(last_updated_by)
  REFERENCES studentdb.database_user (database_user_id));

You can run it from a file by calling the sqlcmd utility. You’ll need to know several things to run it. First, you need to know your database instance. You can capture that from a query against the data dictionary or catalog. Just run the following from inside the Microsoft SQL Server Management Studio (SSMS):

SELECT @@SERVERNAME;

In my case, it shows the following, which is the machine’s hostname a backslash and SQLEXPRESS:

MCLAUGHLINSQL\SQLEXPRESS

The script uses sqltest.sql as a file name, and you can call it from the Windows shell environment like this:

sqlcmd -S MCLAUGHLINSQL\SQLEXPRESS -U student -P student -i C:\Data\MicrosoftSQL\sqltest.sql -o C:\Data\Microsoft\sqltest.out

As always, I hope this helps.

Written by maclochlainn

January 15th, 2015 at 7:18 pm

Popular Programming Languages

with 6 comments

First of all, Happy New Year!

IEEE Spectrum published a ranking of the most popular programming languages. Computational journalist Nick Diakopoulos wrote the article. While it may surprise some, I wasn’t surprised to find SQL in the top ten.

07dataflow-1403643424680Nick weighted and combined 12 metrics from 10 sources (including IEEE Xplore, Google, and GitHub) to rank the most popular programming languages.

  • Compiled programming languages (Java [#1], C [#2], C++ [#3], C# [#4], Objective-C [#16])
  • Interpreted programming languages (Python [#5], JavaScript [#6], PHP [#7], Ruby [#8], Perl [#11], HTML [#12])
  • Data languages (SQL [#9], MATLAB [#10], R [#13])

I couldn’t resist including Objective-C because it shows how the iPhone, iPad, and Mac OS impact our daily lives. At the same time, Assembly [#15] is actually more popular than Objective-C. Shell [#17] follows Objective-C. While the Visual Basic [#14] programming language still remains very popular.

There are many “why” questions raised by this list of popular programming languages. The “why” from my perspective deals with what are the market drivers for their popularity. The money drivers I see are as follows:

Business Intelligence (BI) software manages most high-level data analysis tools and they’ll continue to get better over time. However, if SQL has shown us anything over 30 years it’s that ultimately we revert to it to solve problems. The conclusion from the reality of BI probably means the programming languages that develop those tools will continue to rise and so will the underlying data languages.

It’s also interesting to note that nine out of ten of the popular programming languages work with databases, like Oracle, MySQL, PostgreSQL, or SQL Server. While JavaScript doesn’t access the database typically, it’s JSON (JavaScript Object Notation) is supported in all the databases.

Written by maclochlainn

January 1st, 2015 at 9:46 pm