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