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.6 Ada 2012	Postgres 9.1
show version	$ fpc -v	$ gnatgcc -gnat12 --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; $ gnatmake -gnat12 hello.adb $ ./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	source: foo.ads: specification foo.adb: body compiler generated: foo.adi: compilation information foo.o: object foo
source code encoding		-- default is ISO 8859-1: pragma Wide_Character_Encoding(UTF8)
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	none
variables and expressions
	pascal	ada	plpgsql
are identifiers 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; N: 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 N: Integer; begin N := 7; end;	declare i integer := 3; begin raise notice 'i is %', i; end;
assignment	X := 1;	X := 1;	x = 1;
pointer declaration	IP: ^Integer;	type Integer_Ptr is access Integer; Ip: Integer_Ptr;	none
allocate heap	new(IP);	Ip := new Integer;	none
free heap	dispose(IP);	with Ada.Unchecked_Deallocation; procedure Free_Example is type Integer_Ptr is access Integer; procedure Free is new Ada.Unchecked_Deallocation( Integer, Integer_Ptr); Ip: Integer_Ptr; begin Ip := new Integer; Ip.all := 7; Free(Ip); end Hello;	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	-- Ada 2012: (if X > 0 then X else -X)	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	Integer	smallint: 2 bytes integer: 4 bytes bigint: 8 bytes
float type	Real	Float	real: 4 bytes double precision: 8 bytes
fixed type	four fractional digits: Currency	type Currency is delta 0.01 digits 18; Amt: Currency; Amt := 3.99;	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])	Integer'Min(1, 2) Integer'Max(1, 2) Integer'Min(1, Integer'Min(2, 3)) Integer'Max(1, Integer'Max(2, 3)) Float'Min(1.0, Float'Min(2.0, 3.0)) Float'Max(1.0, Float'Max(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	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	2 ^ 16
sqrt	Sqrt(2)	with Ada.Numerics.Elementary_Functions; use Ada.Numerics.Elementary_Functions; -- no implicit type conversion of Integer to Float: Sqrt(2.0)	sqrt(2)
sqrt -1	raises EInvalidOp	raises Ada.Numerics.Argument_Error	raises invalid_argument_for_power_function
transcendental 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) 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 6 String(1..6) Wide_Wide_String(1..6)	pads length to n with spaces: char(n)
bounded length string type			error if n exceeded: varchar(n)
unbounded length string type		with Ada.Strings.Unbounded; Ada.Strings.Unbounded.Unbounded_String	text
character type		Character 1 byte Wide_Wide_Character 4 bytes	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)	Integer'Image(8) Float'Image(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 datetime		with Ada.Calendar; Now: Ada.Calendar.Time; Now := Ada.Calendar.Clock;	now()
datetime to string		with Ada.Calendar.Formating; -- "2015-03-29 17:46:35": Ada.Calendar.Formatting.Image(Now);	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;
user-defined 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
generic types
	pascal	ada	plpgsql
functions
	pascal	ada	plpgsql
declare		function Add(M: Integer; N: Integer) return Integer;
define	function Add(M: Integer; N: Integer): Integer; begin Result := M + N; end;	function Add(M: Integer; N: Integer) return Integer is begin return M + N; end Add;	create function add(i int, j int) returns int as $$ begin return i + j; end; $$ language plpgsql;
call	Sum := Add(7, 3);	Sum := Add(7, 3);	-- in select clause: select add(1, 2); -- in where clause: select * from cust where id = add(1, 2); -- inside PL/pgSQL functions can be used wherever -- expressions are permitted. Can be used as a statement -- with perform: perform add(1, 2);
undefine function	none	none	-- parameter types are required: drop function foo(int, int);
no return value	procedure Message(Msg: String); begin WriteLn(Msg); end;	procedure Message(Msg: String) is begin Text_IO.Put_Line(Msg); end Message;	-- 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.	procedure Incr(N: in out Integer) is begin N := N + 1; end Incr; I := 3; Incr(3);	none
pass uninitialized variable by reference		procedure Get_Pi(Pi: out Float) is begin Pi := 3.14; end Get_Pi;	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
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;
switch
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	break	exit	exit
continue	continue		continue
goto
exceptions
	pascal	ada	plpgsql
predefined exceptions		Constraint_Error Program_Error Storage_Error Tasking_Error
define exception	{$mode delphi} type Err42 = class(Exception);	Err42: 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;
raise exception	{$mode delphi} raise Exception.Create('bad int: ' + IntToStr(7));	raise Err42;	-- 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;
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		with Text_IO; with Ada.Wide_Wide_Text_IO; Text_IO.Put_Line("Hello!"); Ada.Wide_Wide_Text_IO.Put_Line("Hello!");	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
directories
	pascal	ada	plpgsql
processes and environment
	pascal	ada	plpgsql
command line arguments		with Ada.Command_Line; Ada.Command_Line.Command_Name Ada.Command_Line.Argument_Count Ada.Command_Line.Argument(1) Ada.Command_Line.Argument(2)
libraries and namespaces
	pascal	ada	plpgsql
load library		with Text_IO;
namespace declaration		$ cat put_hello.ads package Put_Hello is procedure Put_Hello; end; $ cat put_hello.adb cat put_hello.adb with Text_IO; package body Put_Hello is procedure Put_Hello is begin Text_IO.Put_Line("Hello World!"); end Put_Hello; end;
namespace alias		with Text_IO; procedure Hello is package IO renames Text_IO; begin IO.Put_Line("Hello World!"); end Hello;
unqualified import of namespace		use Text_IO;
	________________________________________________________	________________________________________________________	________________________________________________________

General Notes

version used

The version used in this reference sheet.

Grammar and Invocation

hello word

A "Hello, World!" example.

ada:

gnatmake takes care of compiling everything that the final executable depends on and linking the final executable.

It is possible to compile and link in separate steps:

$ cat hello.adb
with Text_IO; use Text_IO;
procedure Hello is
begin
  Put_Line ("Hello World!");
end Hello;

$ gnatgcc -gnat12 -c hello.adb

$ gnatbind -gnat12 hello

$ gnatlink -gnat12 hello

$ ./hello
Hello World!

gnatgcc creates the files hello.ali and hello.o, and gnatlink creates the final executable hello.

gnatbind creates the files b~hello2.adb and b~hello2.ads. It is a necessary precursor to gnatlink. gnatbind prevents linking a program in which objects are using incompatible versions of the same header. This is a safety feature not provided by C and C++ linkers.

file suffixes

Customary suffixes for source and object files.

ada:

The .adi file is generated whenever a .o file is generated. The .o file is what goes into the final executable. The .adi file contains information used by the compiler, including the flags that were used to comile the .o file and what files it depends on.

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

User-Defined Types

Generic 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.

Files

Directories

Processes and Environment

Libraries and Namespaces

Pascal

Free Pascal: Reference Guide
Free Pascal: Run-Time Language Reference

Ada

Ada Reference Manual
GNAT Reference Manual
GNAT User's Guide for Native Platforms

Ada has about 60 reserved words; these are written with lower case letters. Other identifiers are written with underscores and capitalization, e.g. Put_Hello.

Files use lower case letters and underscores, but are otherwise named after the package they contain. The package Put_Hello would go in the file put_hello.adb, with its specification in put_hello.ads.

PL/SQL

PostgreSQL 9.1 Documention: PL/pgSQL
Porting from PL/SQL to PL/pgSQL

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

Execute SQL in a 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