Pascal Style Languages: Pascal, Ada, PL/pgSQL, MySQL

a side-by-side reference sheet

arithmetic and logic | strings | regexes | dates and time | arrays | records | functions | execution control | files | directories
processes and environment | libraries | database | contact

pascal (1970, 1991) ada (1979, 1995) plsql (1988, 1998) mysql (1996)
hello word $ cat hello.pas
program hello;
begin
  writeln('hello world!');
end.
$ fpc hello.pas
$ ./hello
$ cat hello.adb
with Text_IO; use Text_IO;
procedure Hello is
begin
  Put_Line ("Hello World!");
end Hello;
$ gnatgcc -c hello.adb
$ gnatbind hello
$ gnatlink hello
$ ./hello
Hello World!
at psql prompt:
> create or replace function hello()
returns varchar as $$
begin
  return 'Hello World!';
end;
$$ language plpgsql;
CREATE FUNCTION
> select hello();
hello
--------------
Hello World!
(1 row)
at mysql prompt:
> create function hello() returns varchar(20)
> return 'Hello World!';
Query OK, 0 rows affected (0.00 sec)
> select hello();
--------------
Hello World!
1 row in set (0.10 sec)
version used Free Pascal 2.4 GNAT GCC 4.1 Postgres 8.4 MySQL 5.5 with this setting:
> set sql_mode='ANSI';
version
 
$ fpc -v $ gnatgcc --version $ psql --version $ mysql5 --version
comment (* comment line
another line *)
{ comment line
another line }
-- comment line
-- another line
-- comment line
-- another line
-- comment line
-- another line
case sensitive
 
no no no no
declare constant, type, and variable program foo;
const
  PI : real = 3.14159;
type
  customer = record
    id : integer;
    name : string;
  end;
var
  i : integer;
  c : customer;
begin
  body of program
procedure Foo is
  Pi : constant FLOAT := 3.14;
  i : INTEGER;
  type Customer is record
    Id : INTEGER;
    Name : String(1..4);
  end record;
  C: Customer;
begin
  body of program
create type customer as (
  id integer,
  name text
);

create or replace function foo() returns void as $$
declare
  pi numeric(10,4) = 3.14;
  i integer = 42;
  c customer%rowtype;
begin
  return;
end $$ language plpgsql;
delimiter |

create function foo()
returns text
begin
  declare pi numeric(10,4);
  declare i int;
  set i = 7;
  return 'hello';
end |

delimiter ;
assignment
 
x := 1; x := 1; x = 1; set x = 1;
pointer declaration ip : ^integer; type Integer_Pointer is access Integer;
Ip : Integer_Pointer;
none none
allocate memory
 
new(ip); Ip := new Integer; none none
free memory
 
dispose(ip); none none
dereference pointer ip^ := 7;
ans := 6 * ip^;
Ip.all := 7;
ans := 6 * Ip.all;
none none
null literal can only be assigned to pointers:
nil
can only be assigned to access types:
null
NULL NULL
null test x = nil x = null x is NULL
x = NULL is always false
x is NULL
x = NULL is always false
coalesce
 
none 7 + coalesce(x, 0) 7 + coalesce(x, 0)
nullif
 
none nullif(x, 0) nullif(x, 0)
conditional expression
 
none case when x > 0 then x else -x end case when x > 0 then x else -x end
arithmetic and logic
pascal ada plsql mysql
boolean type
 
boolean BOOLEAN BOOL BOOLEAN BOOL BOOLEAN
true and false literals true false TRUE FALSE TRUE FALSE TRUE FALSE
falsehoods false
non booleans cause error in boolean context
FALSE
non booleans cause error in boolean context
FALSE NULL 0
strings and floats cause error in boolean context
FALSE NULL 0 0.0
non-zero numbers are TRUE in boolean context; strings are FALSE in boolean context
logical operators
 
and or xor not and or xor not AND OR none NOT AND OR XOR NOT also: && || !
short circuit operators and_then
or_else
and then
or else
AND
OR
none
integer types integer smallint: 2 bytes
integer: 4 bytes
bigint: 8 bytes
floating point types real real: 4 bytes
double precision: 8 bytes
fixed point types
 
numeric(digits, fractional_digits)
relational operators = <> < > <= >= = /= < > <= >= = != also: <> < > <= >= = != also: <> < > <= >=
convert string to number uses sysutils;
7 + StrToInt('12')
73.9 + StrToFloat('.037')
arithmetic operators automatically convert strings to numbers
cast('12' as int)
cast('3.14') as real)
convert number to string uses sysutils;
'value: ' + IntToStr(8)
'value: ' + FloatToStr(3.14)
double pipe operator || converts numbers to strings automatically
cast(8 to text)
cast(3.14 to text)
arithmetic operators + - * / div mod none + - * none / mod or rem ** + - * ?? / % ^ + - * / div % pow(base,exp)
float division of integers 3 / 7 Float(3)/Float(7) 3 * 1.0 / 7 3 / 7
arithmetic functions sqrt exp ln sin cos ?? ?? ?? arctan ?? in context clause:
with Ada.Numerics.Elementary_Functions;
use Ada.Numerics.Elementary_Functions;
available functions:
Sqrt Exp Log Sin Cos Tan Arcsin Arccos none Arctan
sqrt exp ln sin cos tan asin acos atan atan2 sqrt exp ln sin cos tan asin acos atan atan2
arithmetic truncation round trunc ?? ?? return Float:
Float'Rounding ?? Float'Ceiling Float'Floor
round trunc ceil floor round truncate(numeric, 0) ceil floor
min and max least(1,2,3)
greatest(1,2,3)
least(1,2,3)
greatest(1,2,3)
division by zero
 
raises EDivByZero raises CONSTRAINT_ERROR raises division_by_zero NULL
integer overflow
 
modular arithmetic modular arithmetic raises numeric_value_out_of_range modular arithmetic
float overflow
 
raises EOverflow +Inf******* raises numeric_value_out_of_range NULL
sqrt -2 raises EInvalidOp raises ADA.NUMERICS.ARGUMENT_ERROR raises invalid_argument_for_power_function NULL
random integer, random float random(100)
random
with Ada.Numerics.Float_Random;
with Ada.Numerics.Discrete_Random;
use Ada.Numerics;
procedure Foo is
  type Rand_Range is range 0..99;
  package Rand_Int is new Discrete_Random(Rand_Range);
  IG : Rand_Int.Generator;
  FG : Float_Random.Generator;
begin
  use Rand_Int.Random(IG)
  use Float_Random.Random(FG)
floor(100 * random())
random()
floor(100 * rand())
rand()
bit operators
 
shl shr and or xor not << >> & | ^ ~ << >> & | ^ ~
strings
pascal ada plsql mysql
string literal 'Don''t say "foo"' "Don't say ""foo""" 'Don''t say "foo"' 'Don''t say "foo"'
TRADITIONAL:
"Don't say ""foo"""
fixed length string type error unless string length is n
STRING(1..n)
length can be omitted if initialized with literal in declaration
pads length to n with spaces:
char(n)
pads to length n with spaces
char(n)
bounded length string type error if n exceeded:
varchar(n)
truncates with warning if n exceeded:
varchar(n)
unbounded length string type text none
character type CHARACTER char(1) char(1)
chr and ord chr(65)
ord('A')
chr(65)
ascii('A')
char(65)
ord('A')
concatenate 'hello' + ' world' "hello" & " world" 'hello' || ' world' 'hello' || ' world'
TRADITIONAL:
concat("hello", " world")
length length('hello') length('hello') length('hello')
extract substring copy(s, 1, 4) substr('hello', 1, 4) substr('hello', 1, 4)
index of substring pos('hell', 'hello') strpos('hello', 'hell') locate('hello', 'hell')
case manipulation uses sysutils;
UpperCase('hello')
LowerCase('HELLO')
upper('hello')
lower('HELLO')
upper('hello')
lower('HELLO')
strip Trim(' foo ')
TrimLeft(' foo')
TrimRight('foo ')
trim(' foo ')
ltrim(' foo')
rtrim('foo ')
trim(' foo ')
ltrim(' foo')
rtrim('foo ')
pad on left, pad on right lpad('foo', 10)
rpad('foo', 10)
lpad('foo', 10, ' ')
rpad('foo', 10, ' ')
regular expressions
pascal ada postgresql mysql
dates and time
pascal ada plsql mysql
arrays
pascal ada postgresql mysql
declare array a : array[1..5] of integer; A : array(1..5) of Integer; a int[]; create temporary table a ( i int not null auto_increment, v int, primary key (i) );
array length A'Last array_length(a, 1) declare a_length int;
select count(*) into a_length from a;
array element access a[1] := 3; A(1) := 3; a[1] = 3; declare elem int;
select v into elem from a where i = 1;
array initialization A : array(1..5) of Integer := (1,3,5,2,4); a int[] = array[1,3,5,2,4]; truncate table a;
insert into a (v) values (1), (3), (5), (2), (4);
array slice A(3..4) := A(1..2); a[1:2]
can assign to slice in UPDATE statement but not in assignment
none
array out of bounds behavior undefined; free pascal permits out of bounds memory access compiler warning; raises CONSTRAINT_ERROR at runtime NULL NULL
declare multidimensional array a integer[][];
multidimensional array access a[2][3] = 7;
records
pascal ada postgresql mysql
define record type in type section:
customer = record
  id : integer;
  name : string;
  end;
type Customer is record
  Id : Integer;
  Name : String(1..4);
end record;
create type customer as (
  id integer,
  name text
);
declare record c : customer; C : Customer := ( 17, "John" ); declare
  c customer;
begin
  c = (17,'John');
  code which uses c
record member access c.name := 'Fred'; C.Name := 'Fred'; c.name = 'Fred'
record block
functions
pascal ada plsql mysql
block with local scope declare
  i integer := 3;
begin
  raise notice 'i is %', i;
end;
declare function parameters create function foo(i int, j int)
returns int as $$
begin
  code using i and j

alternative method:
create function foo2(int)
returns int as $$
declare
  i alias for $1;
  j alias for $2;
begin
  code using i and j
procedure declaration declare return type as void:
create or replace function msg(
  msg text
)
returns void as $$
begin
  raise notice '%', msg;
end;
$$ language plpgsql;
function declaration
drop function drop function foo(); drop function foo;
call by reference
nested functions
overloading
forward declaration
execution control
pascal ada plsql mysql
if if i = 0 then
begin
  writeln('no hits');
end
else
  if i = 1 then
  begin
    writeln('one hit');
  end
  else
    writeln(IntToStr(i) + ' hits');
if I = 0 then
  Put_Line("no hits");
elsif I = 1 then
  Put_Line("one hit");
else
  Put_Line(Integer'Image(I) & " hits");
end if;
if i = 0 then
  return 'no hits';
elsif i = 1 then
  return 'one hit';
else
  return i || ' hits';
end if;
if i = 0 then
  return 'no hits';
elseif i = 1 then
  return 'one hit';
else
  return i || ' hits';
end if;
while i := 0;
while i < 10 do
begin
  writeln(IntToStr(i));
  i := i + 1;
end
I := 0;
while I < 10 loop
  Put_Line(Integer'Image(I));
  I := I + 1;
end loop;
i = 1;
sum = 0;
while i <= n loop
  sum = sum + i;
  i = i + 1;
end loop;
declare s int default 0;
declare i int default 1;
while i<=n do
  set s = s + i;
  set i = i + 1;
end while;
for for i := 0 to 9 do
  writeln(IntToStr(i));
for I in 1..9 loop
  Put_Line(Integer'Image(I));
end loop;
sum = 0;
for i in 1..n loop
  sum = sum + i;
end loop;
none
break and continue exit continue
files
pascal ada plsql mysql
write to stdout raise notice 'i is %', i;
directories
pascal ada plsql mysql
processes and environment
pascal ada plsql mysql
libraries
pascal ada plsql mysql
database
postgresql mysql
command line sql prompt psql -U user -d database -h host mysql5 -u user database
sql prompt help \? ?
run sql script \i foo.sql source foo.sql
redirect output to file \o bar.txt
select * from bar;
\o
tee bar.txt
select * from bar;
notee
switch database \connect database; use database
show databases select datname from pg_database; show databases;
show tables \d show tables;
describe table \d table describe table
show functions \df;
show function source \df+ func_name;
__________________________________________ __________________________________________ __________________________________________ __________________________________________

General Notes

hello word

A "Hello, World!" example.

version used

The version used in this reference sheet.

version

How to get the version.

comment

The syntax for a comment.

case sensitive

Are identifiers case sensitive?

declare constant, type, variable

How to declare a constant, type, or variable.

assignment

The syntax for assigning a value to a variable.

pointer declaration

How to declare a pointer.

Arithmetic and Logic

Strings

string literal

string concatenate

Regular Expressions

Dates and Time

Arrays

Records

Functions

Execution Control

if

The syntax for an if statement.

while

The syntax for a while statement.

for

The syntax for a for loop.

break and continue

How to break from a loop; how to jump to the next iteration of a loop.

Files

Directories

Processes and Environment

Libraries

Database

command line

postgresql:

If the database user is not specified, it will default to the operating system user. If the database is not specified, it will default to the operating system user. If the host is not specified, psql will attempt to connect to a server on the local host using a Unix domain socket.

mysql:

If the database user is not specified, it will default to the operating system user. If the database is not specified, a database must be provided at the command prompt before work can be done.

sql prompt help

How to get a list of commands available at the SQL prompt.

run sql script

How to run a SQL script at the command line.

redirect output to file

How to redirect the output of the SQL prompt to a file.

switch database

How to switch between databases when using the command line SQL prompt.

show database

List the available databases on a server.

show tables

List the tables in the current database.

describe table

Show the columns for a table and their types.

show functions

List the stored functions in the current database.

show function source

Algol

The Syntax and Semantics of the Proposed International Algebraic Language of the Zurich ACM-GAMM Conference Backus 1959
Revised Report on the Algorithm Language Algol 60 Naur 1960
Algol-W Manual (pdf)
Burroughs Extended Algol Reference Manual 1962, 1966

Pascal

Pascal Tutorial
The Programming Language Pascal Wirth 1973
ISO 7185 1983,1990
The P4 Compiler and Interpreter
UCSD Pascal System II Manual 1979
UCSD System I Source Code 1978
Why Pascal is Not My Favorite Language Kernighan 1981
Summary of ISO 7185 (1983) and ISO 10206 (1991)
Free Pascal Documentation

The design goals of Pascal were to "make available a language suitable to teach programming" and to develop a language which is "both reliable and efficient on presently available computers."

Pascal is based on Algol-60. It adds user defined types such as records and enumerations. Pascal forbids forward references which permits compilation with a one-pass compiler. The first working compiler appeared in 1970 and ran on the CDC 6000. The CDC 6000 used a 6-bit character set that did not have curly braces, part of the reason perhaps that Wirth chose keywords to delimit blocks.

From its earliest implementations, Pascal compilers were often implemented in Pascal. A series of compilers called P1, P2, P3, and P4 were developed in Zurich in the early 1970s which used an intermediate representation called P-code as a portability aid. The backend could be replaced by a virtual machine, and this technique was employed by the UCSD p-System, which appeared in 1978. The UCSD p-System design influenced Java and the JVM.

The language was standardized by ANSI and ISO in 1983. That same year Turbo Pascal was released for MS-DOS. Turbo Pascal was fast and easy to use. Pascal was used in the development of the Macintosh with some hand translation of Pascal code into assembly. Apple developed an object oriented extension of Pascal and shipped a framework called MacApp which used it in 1986. Object Pascal and MacApp were supported until the switch to the PowerPC architecture in 1994. Objects were added to Turbo Pascal for Mac in 1986, and for DOS Turbo Pascal in 1989. Extensions to Pascal made by Borland and others were incorprated into the Extended Pascal ISO standard in 1991. Object oriented extensions to Pascal have not been standardized, however. Borland renamed their product Delphi in 1995.

Modula-2

Modula-2 Tutorial
Modula-2 Reference

After spending a year at Xerox PARC, Wirth decided to build his own workstation modeled on the Alto. For this workstation Wirth designed a new language which he described "Programming in Modula-2" (1982). Modula-2 was Pascal with a few new features, the most import of which was the module which is both a unit of compilation and a scope for identifier bidings. A client of a module can import qualified or unqualified identifier bindings into its own scope. The syntax for doing this is the same as that used by Python. Unlike Python, a module has control over which bindings can be imported by a client.

Ada

Ada Tutorial
Rationale for the Design of the Ada Programming Language 1983
Ada 83 Reference Manual
Ada 95 Reference Manual
Ada 2005 Reference Manual (pdf)
GNAT 4.1.2 Reference Manual
History of the Ada Programming Language

In 1979 the US Department of Defense, in an effort to reduce the number of programming languages used in defense projects, awarded a project to Honeywell to design and develop a new high level language called Ada. The DoD approved the Ada reference manual in 1980. The language received ANSI standardization in 1983 and the object oriented extension received ANSI/ISO standardization in 1995. The DoD had regulations requiring the use of Ada from 1987 to 1997.

VHDL

Initially specified in 1987.

Modula-3

Modula-3 Report Cardelli etal. 1989

PL/SQL

Oracle 11g PL/SQL Language Reference
Oracle 11g PL/SQL Packages and Types Reference
PostgreSQL 8.4 Documention: PL/pgSQL
Porting from PL/SQL to PL/pgSQL

Oracle 6 (1988) introduced PL/SQL. Oracle 7 (1992) added stored procedures. PostgreSQL 6.4 (1998) added PL/pgSQL.

PL/SQL is an extension to SQL in that SQL statements can be used as statements in the body of a PL/SQL procedure. PL/SQL variables which are in scope can be used in the SQL statement, and as a result it using variables names which are the same as table, column, or function names should be avoided.

MySQL

MySQL 5.1 Reference Manual: Compound-Statement Syntax

The SQL3 standard (1999) contains a procedural extension called SQL/PSM. MySQL implemented it in 2005. Postgres added an implementation called PL/pgPSM in 2007.

MySQL does not by default implement the ANSI standard. If the user has SUPER privilege, the server can be toggled between ANSI and TRADITIONAL mode:

> set global sql_mode='ANSI';
> set global sql_mode='TRADITIONAL';

This can also be done at the level of the session:

> set session sql_mode='ANSI';
> set session sql_mode='TRADITIONAL';
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License