Pascal Style Languages: Pascal, Ada, PL/pgSQL

a side-by-side reference sheet

grammar and invocation | variables and expressions | arithmetic and logic | strings | regexes | dates and time | arrays | user-defined types | generic types | functions | execution control | exceptions | file handles | files | directories | processes and environment | libraries and namespaces

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.

show version

How to get the version.

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
issue tracker | content of this page licensed under creative commons attribution-sharealike 3.0