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 | functions | execution control | exceptions | file handles | files | sql | directories | processes and environment | libraries and namespaces

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
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)
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;
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
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.

show version

How to get the version.

Grammar and Invocation

hello word

A "Hello, World!" example.

file suffixes

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

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

SQL

Directories

Processes and Environment

Libraries and Namespaces

Pascal

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

The Programming Language Pascal Wirth 1973
ISO 7185 1983,1990
Why Pascal is Not My Favorite Language Kernighan 1981
Summary of ISO 7185 (1983) and ISO 10206 (1991)

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

PL/SQL

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

issue tracker | content of this page licensed under creative commons attribution-sharealike 3.0