Sintesi DDL

Tipi:
1 INTEGER,SMALLINT
2 NUMERIC(precisione,scala) o DECIMAL(p,s) p=n# totale cifre, scala = n# cifre decimali
3 REAL, DOUBLE PRECISION, FLOAT (4B, 8B, 4B-8B)
4 CHARACTER[(n)] o CHAR[(n)]
5 CHARACTER VARYING[(n)] - VARCHAR [(n)]
6 BIT[(n)]
7 BIT VARYING [(n)]
8 BOOLEAN
9 DATE
10 TIME
11 TIMESTAMP (date+time)
12 BLOB (bytea (sequenza di byte) o oid (tabella a parte con pointer))
13 CLOB (text)

CREATE SCHEMA [NomeSchema] [AUTHORIZATION Username] [{Elemento Schema}]
CREATE DATABASE NomeDB [[WITH][OWNER [=] Username][ENCODING [=] encoding]
CREATE TABLE NomeTabella({NomeAttr Dominio [DEFAULT DefaultValue] [Vincoli]}) 
	DefaultVAlue: val|User|Null
	Vincoli: NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES(FOREIGN KEY) [ON UPDATE/DELETE CASCADE/SET NULL/SET DEFAULT/NO ACTION(def)], CHECK(qualcosa)
CREATE DOMAIN NomeDominio AS TipoDatoBase [DefaultValue][{Vincoli}]

ALTER TABLE NomeTabella ALTERBLABLA

DROP BlaBla [RESTRICT|CASCADE] (restrict=solo se vuoto)

UPDATE <nome> SET <col> = {expr | DEFAULT} [WHERE cond];

CREATE OR REPLACE FUNCTION NomeFunz ([<par>], [OUT <out>]) [RETURNS <tipo>] AS $$
	<corpo>
$$ LANGUAGE plpgsql;

se <par> non ha some $1, $2, ... $n

<corpo> =
	[DECLARE] variabili;
	BEGIN
		[EXCEPTION WHEN]
	END;

variabili =
	<nome> [CONSTANT] <tipo> [NOT NULL] [{DEFAULT | := } expr];

	<nome> <nometab.nomeattr>%TYPE;
	<nome> <nometab>%ROWTYPE;

commenti: -- o /* */

IF THEN ELSIF ELSE END IF;

LOOP istr [EXIT | EXIT WHEN] END LOOP;

WHILE LOOP END LOOP;

FOR <var> IN [REVERSE] <exprin>..<exprend> [BY <exprpasso>] LOOP END LOOP;


CREATE TRIGGER <nome> {BEFORE | AFTER} <evento> ON <tabella> [FOR EACH {ROW | STATEMENT}] EXECUTE PROCEDURE <nomefunz>

<evento> = {INSERT|UPDATE|DELETE} (posso usare OR)

<nomefunz> deve essere una funzione del tipo 
	CREATE OR REPLACE FUNCTION <nome>(NO_PAR) RETURNS TRIGGER AS $$ $$ LANGUAGE plpgsql;

nella funzione posso usare le var NEW e OLD.

RAISE <lvl> '<mex>'

<lvl> = DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION

DEBUG = solo se in DEBUG
LOG = solo sul log
INFO = stdopt
NOTICE = log e stdopt
WARNING = debug, log, stdopt
EXCEPTION = blocca