Pascal Style Languages: Pascal, Ada, PL/pgSQL

a side-by-side reference sheet

	pascal	ada	plpgsql
version used	Free Pascal 2.4	GNAT GCC 4.1	Postgres 9.1
show version	$ fpc -v	$ gnatgcc --version	$ psql --version
grammar and invocation
	pascal	ada	plpgsql
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)
file suffixes	foo.pas foo.o foo
end-of-line comment	not ISO Pascal: // comment line // another line	-- comment line -- another line	-- comment line -- another line
multiple line comment	(* comment line another line ) { comment line* another line }		none
variables and expressions
	pascal	ada	plpgsql
case sensitive	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;
block with local scope			declare i integer := 3; begin raise notice 'i is %', i; end;
assignment	X := 1;	x := 1;	x = 1;
pointer declaration	IP: ^Integer;	type Integer_Pointer is access Integer; Ip : Integer_Pointer;	none
allocate memory	new(IP);	Ip := new Integer;	none
free memory	dispose(IP);		none
dereference pointer	IP^ := 7; Ans := 6 * IP^;	Ip.all := 7; ans := 6 * Ip.all;	none
null literal	can only be assigned to pointers: nil	can only be assigned to access types: null	NULL
null test	X = nil	x = null	x is NULL x = NULL is always false
coalesce	none		7 + coalesce(x, 0)
nullif	none		nullif(x, 0)
conditional expression	none		case when x > 0 then x else -x end
arithmetic and logic
	pascal	ada	plpgsql
boolean type	Boolean	BOOLEAN	BOOL BOOLEAN
true and 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
logical operators	and or xor not	and or xor not	AND OR none NOT
short circuit operators	{ in Free Pascal 'and' and 'or' also short circuit } and_then or_else	and then or else	AND OR
integer type	Integer		smallint: 2 bytes integer: 4 bytes bigint: 8 bytes
float type	Real		real: 4 bytes double precision: 8 bytes
fixed type	four fractional digits: Currency		numeric(digits, fractional_digits)
relational operators	= <> < > <= >=	= /= < > <= >=	= != also: <> < > <= >=
min and max	uses Math; Min(1, 2) Max(1, 2) MinIntValue([1, 2, 3]) MaxIntValue([1, 2, 3]) MinValue([1.0, 2.0, 3.0]) MaxValue([1.0, 2.0, 3.0])		least(1,2,3) greatest(1,2,3)
arithmetic operators	+ - * / div mod	+ - * none / mod or rem	+ - * none / %
integer division	7 div 3		7 / 3
integer division by zero	raises EDivByZero	raises CONSTRAINT_ERROR	raises division_by_zero
float division	7 / 3	Float(7)/Float(3)	7 * 1.0 / 3
float division by zero	+Inf	raises CONSTRAINT_ERROR	raises division_by_zero
power	uses Math; Power(2, 16);	**	2 ^ 16
sqrt	Sqrt(2)		sqrt(2)
sqrt -1	raises EInvalidOp	raises ADA.NUMERICS.ARGUMENT_ERROR	raises invalid_argument_for_power_function
transcendantal functions	uses Math; Exp Ln Sin Cos Tan ArcSin ArcCos ArcTan ArcTan2	with Ada.Numerics.Elementary_Functions; use Ada.Numerics.Elementary_Functions; Exp Log Sin Cos Tan Arcsin Arccos none Arctan	exp ln sin cos tan asin acos atan atan2
float truncation towards zero, to nearest integer, down, up	uses Math; Trunc Round Floor Ceil	return Float: ?? Float'Rounding Float'Floor Float'Ceiling	trunc round floor ceil
absolute value and sign	Abs(-7) none		abs(-7) sign(-7)
integer overflow	modular arithmetic	modular arithmetic	raises numeric_value_out_of_range
float overflow	raises EOverflow	+Inf*******	raises numeric_value_out_of_range
random number integer, 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()
bit operators	shl shr and or xor not		<< >> & \| ^ ~
strings
	pascal	ada	plpgsql
string literal	'Don''t say "foo"'	"Don't say ""foo"""	'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)
bounded length string type			error if n exceeded: varchar(n)
unbounded length string type			text
character type		CHARACTER	char(1)
chr and ord	Chr(65) Ord('A')		chr(65) ascii('A')
concatenate	'hello' + ' world'	"hello" & " world"	'hello' \|\| ' world'
length	Length('hello')		length('hello')
extract substring	Copy(S, 1, 4)		substr('hello', 1, 4)
index of substring	Pos('hell', 'hello')		strpos('hello', 'hell')
case manipulation	uses SysUtils; UpperCase('hello') LowerCase('HELLO')		upper('hello') lower('HELLO')
strip	Trim(' foo ') TrimLeft(' foo') TrimRight('foo ')		trim(' foo ') ltrim(' foo') rtrim('foo ')
pad on left, pad on right			lpad('foo', 10) rpad('foo', 10)
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 operands to strings cast(8 to text) cast(3.14 to text)
regular expressions
	pascal	ada	plpgsql
match			select * from pwt where name similar to 'r[a-z]+';
substitute			select regexp_replace('foo bar', 'bar$', 'baz');
extract subgroup			select (regexp_matches('foobar', '(f..)bar'))[1];
dates and time
	pascal	ada	plpgsql
current date and time			now()
datetime to string			to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
string to datetime			to_timestamp('2011-09-26 00:00:47', 'YYYY-MM-DD HH24:MI:SS')
arrays
	pascal	ada	plpgsql
declare array	A: array[1..5] of Integer;	A : array(1..5) of Integer;	a int[];
array length		A'Last	array_length(a, 1)
array element access	A[1] := 3;	A(1) := 3;	a[1] = 3;
array initialization		A : array(1..5) of Integer := (1,3,5,2,4);	a int[] = array[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
array out of bounds behavior	undefined; free pascal permits out of bounds memory access	compiler warning; raises CONSTRAINT_ERROR at runtime	NULL
declare multidimensional array			a integer[][];
multidimensional array access			a[2][3] = 7;
algebraic data types
	pascal	ada	plpgsql
type synonym	type CustomerId = Integer;
enumerated type	type Direction = (North, South, East, West); var Wind: Direction = North; begin WriteLn(Wind); { prints 'North' }		create type direction as enum ( 'north', 'south', 'east', 'west'); create table wind ( origin direction, speed_mph real ); insert into wind values ( 'north', 12 );
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	plpgsql
define function	function Foo(M: Integer; N: Integer): Integer; begin Result := M + N; end;		create function foo(i int, j int) returns int as $$ begin return i + j; end; $$ language plpgsql;
invoke function	Retval := Foo(7, 3);		-- in select clause: select foo(1, 2); -- in where clause: select * from cust where id = foo(1, 2); -- inside PL/pgSQL functions can be used wherever -- expressions are permitted. Can be used as a statement -- with perform: perform foo(1, 2);
undefine function	none		-- parameter types are required: drop function foo(int, int);
no return value	procedure Message(Msg: String); begin WriteLn(Msg); end;		-- declare return type as void: create or replace function message(msg text) returns void as $$ begin raise notice '%', msg; end; $$ language plpgsql;
pass by reference	{ declare parameter with var } procedure Incr(var N: Integer); begin N := N + 1; end; var I: Integer; begin I := 3; Incr(I); WriteLn(I); { prints 4 } end.		none
nested function			none
overloaded function			--integer version: create or replace function add(m int, n int) returns int as $$ begin return m + n; end; $$ language plpgsql; --float version: create or replace function add(x real, y real) returns real as $$ begin return x + y; end; $$ language plpgsql;
forward declaration	function Odd(N: Integer): Boolean; Forward; function Even(N: Integer): Boolean; begin if N = 0 then Result := true else Result := Odd(N - 1); end; function Odd(N: Integer): Boolean; begin if N = 0 then Result := false else Result := Even(N - 1); end;		none
out parameter
execution control
	pascal	ada	plpgsql
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;
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;
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;
break and continue			exit continue
exceptions
	pascal	ada	plpgsql
raise exception	{$mode delphi} raise Exception.Create('bad int: ' + IntToStr(7));		-- raises exception with condition raise_exception: raise exception 'bad int: %', i; -- also possible to use predefined condition: raise data_exception using message = 'bam!
re-raise exception	{$mode delphi} try risky(); except on E: Exception do begin WriteLn('risky failed'); raise; end;		begin perform risky(); exception when others then raise notice 'risky() failed.'; raise; end;
define exception	{$mode delphi} type Err42 = class(Exception);		-- code string must be five digits or uppercase letters: begin raise 'User defined error 42' using errcode = 'ERR42'; exception when sqlstate 'ERR42' then raise notice 'caught ERR42'; end;
handle any exception	{$mode delphi} try raise Exception.Create('bad int: ' + IntToStr(7)); except on E: Exception do WriteLn(E.Message);		begin raise exception 'bad int: %', i; exception when others then raise notice 'caught bad int'; end;
handle exception by type	{$mode delphi} try raise Err42.Create('bad int: ' + IntToStr(7)); except on E: Err42 do WriteLn(E.Message);		begin n := 1 / 0; exception when division_by_zero then raise notice 'ignoring div by zero'; end;
multiple exception handlers
finally
file handles
	pascal	ada	plpgsql
read line from stdin			none
write line to stdout			none
write line to stderr			-- stderr stream goes to both client and server log. -- -- levels are: debug, log, info, notice, warning -- raise notice 'i is %', i;
files
	pascal	ada	plpgsql
sql
	pascal	ada	plpgsql
execute statement with no result			-- create table foo (s text, i int); create or replace function insert_foo(s text, i int) returns void as $$ begin insert into foo values (s, i); end; ) $$ language plpgsql; -- insert, update, and delete statements raise an -- exception if they fail
select one row
select multiple rows
execute sql in string			create or replace function drop_table(tbl text) returns void as $$ begin execute 'drop table ' \|\| quote_ident(tbl); end; $$ language plpgsql;
functions to prevent sql injection			quote_literal quote_nullable quote_ident
directories
	pascal	ada	plpgsql
processes and environment
	pascal	ada	plpgsql
libraries and namespaces
	pascal	ada	plpgsql
	________________________________________________________	________________________________________________________	________________________________________________________

General Notes

version used

The version used in this reference sheet.

Grammar and Invocation

end-of-line comment

The syntax for a comment which ends at the end of the line.

pascal:

The // style comment is supported by Borland compilers and Free Pascal.

multiple line comment

The syntax for a comment which can span multiple lines.

Variables and Expressions

case sensitive

Are identifiers case sensitive?

pascal:

Free Pascal Reserved Words and Modifiers

A word in Pascal is reserved if it cannot be redefined by the programmer. The names for the built-in types: Integer, Boolean, etc. are not reserved. They are defined in the System unit and can be redefined by the programmer.

Although Pascal is case insensitive, reserved words and modifiers are customarily written in lower case. Other identifiers are customarily written in upper camel case, also known as Pascal case.

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

boolean type

The boolean type.

true and false

The literals for true and false.

falsehoods

Values which evaluate as false in a boolean context.

logical operators

The logical operators for conjunction, disjunction, exclusive or, and negation.

short circuit operators

Short circuit versions of the logical operators.

The short circuit version of and will not evaluate its second argument if the first is false. The short circuit version of or will not evaluate its second argument if the first is true.

integer type

float type

fixed type

relational operators

min and max

arithmetic operators

integer division

integer division by zero

float division

float division by zero

power

sqrt

sqrt -1

transcendental functions

float truncation

absolute value

integer overflow

float overflow

random number

bit operators

Strings

string literal

string concatenate

Regular Expressions

Dates and Time

Arrays

Algebraic Data Types

Functions

define function

How to define a function.

postgresql:

If "or replace" is omitted from the function definition and the function already exists, the statement fails with an error.

Before PostgreSQL 8.0 parameters could not be assigned names in the function signature. The following syntax which is still valid was used:

create or replace function foo(int, int)
returns int as $$
declare
@<&nbsp;&nbsp;>@i alias for $1;
@<&nbsp;&nbsp;>@j alias for $2;
begin
@<&nbsp;&nbsp;>@return i + j;
end;
$$ language plpgsql;

invoke function

How to invoke a function.

undefine function

How to undefine a function.

no return value

How to define a function with no return value. Sometimes such functions are called procedure. Such a function is not useful unless it has a side effect.

pass by reference

How to pass a variable by reference. This permits the callee to modify the value in the variable.

nested function

How to define a function inside another function.

plpgsql:

Nested functions are a feature of Oracle's PL/SQL. A nested function has access to the local variables of the containing function; it is not visible or callable from outside of the containing function.

overloaded function

How to define multiple versions of a function, with the correct version chosen by the type of the arguments used at invocation.

forward declaration

How to declare a function before it is defined.

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.

Exceptions

raise exception

How to raise an exception.

postgresql:

The raise statement can also be used to write info and warning messages. See write line to stderr.

re-raise exception

How to re-raise a caught exception.

postgresql:

PostgreSQL exceptions do not have a stack trace or preserve the line number at which the error originated, so re-raising an exception just preserves the exception type and message.

define exception

How to define a new exception type.

postgresql:

Any 5 character string of digits or uppercase letters may be used as an error code. Many error codes are already in use:

PostgreSQL Error Codes

handle any exception

How to handle any exception.

handle exception by type

How to handle exceptions of a specific type.

multiple exception handlers

File Handles

read line from stdin

How to read a line from standard input.

plpgsql:

In a SQL session the stream on which the server receives SQL commands from the client can be regarded as the standard input. Furthermore the COPY command provides a mechanism for providing unquoted data on the input stream for insertion into a table. PL/pgSQL code does not have access to this stream, however; a COPY command cannot be run from within PL/pgSQL. Note that some PL/pgSQL code runs as triggers in which case no such stream exists.

write line stdout

How to write a line to stdout.

plpgsql:

In an interactive SQL session the server writes the results of SQL select statements to a stream which can be regarded as standard out. PL/pgSQL code cannot write to this stream, however. The only thing PL/pgSQL code can do with selected data is read it into local variables.

write line stderr

How to write a line to stderr.

plpgsql:

The RAISE statement is used to write to stderr. Each message must be assigned one of five log levels: debug, log, info, notice, warning. The message can be reported to the client, written to the server log, or both depending upon how the server is configured.