SQL (Structured Query Language) is a programming language that is widely utilized in accessing and managing relational database systems. It does creation, updating, deletion, insertion, sorting and plenty of other operations to a database, as well as to the tables and views inside the database.
The execution of SQL commands is based on relational database management system (RDBMS), a database management system. Many different versions of SQL are being run on different RDBMS in today's world. Some of the frequently heard RDBMS are MySQL, Microsoft SQL Server, and Oracle Database, etc.
Developed by the PostgreSQL Global Development Group, PostgreSQL is a "powerful, open source object-relational database system" (About Postgres, the PostgreSQL Global Development Group, 1996-2012). As PostgreSQL derives the majority of its syntax from SQL:2008, it largely complies with the ANSI (American National Standards Institute) standards. PgAdmin, an open source GUI (graphical user interface), is the administration tool for PostgreSQL. The version of the PostgreSQL mentioned in this report is 9.0.
The report is based on the premise that the readers possess the fundamental knowledge in SQL, which includes the SQL statements (SELECT * FROM *, INSERT INTO, UPDATE, DELETE), clauses (WHERE, TOP), operators (AND, OR, LIKE), and keywords (JOIN, ORDER BY). Though not required, readers are recommended to have a junior level of relative experience in programming with SQL.
Basic Control Flow in PostgreSQL
The if-then statement should be a familiar statement for computer programmers of all levels. Although the syntax may vary in different programming languages, it is undoubtedly the most basic control flow statement of all.
In PostgreSQL, one of the ways to apply the most basic control flow is:
CASE WHEN (conditional statement) THEN (value returned)
[WHEN (conditional statement) THEN (value returned)]
ELSE (value returned)
The conditional statement denotes a condition that returns boolean value. The value returned refers to the resulting value when the conditional statement evaluates to true.
The following example decodes the account status of personal bank accounts, assuming that '0' means 'Active', '1' means 'Inactive', and '2' means 'Temporarily Frozen'.
WHEN 0 THEN 'Active'
WHEN 1 THEN 'Inactive'
WHEN 2 THEN 'Temporarily Frozen'
The output of the above query will be the readable string account status, instead of a mere digit, that corresponds to each bank account number.
Also, a conditional expression can be put inside another conditional expression: