Fonction pgSQL avec %rowtype

À l'image de son grand frère PL/SQL (Oracle), le langage PL/pgSQL utilisé à l'intérieur des fonctions de PostgreSQL permet de déclarer des variables scalaire (int, varchar, boolean), mais aussi des variables composites en utilisant la syntaxe de déclaration %ROWTYPE.

L'utilisation de %rowtype permet de définir une variable qui contient la structure d'un enregistrement de la table spécifiée. Prenons la table suivante :

CREATE TABLE cities
(
   city_id serial,
   city_name varchar(50),
   province varchar(02),
   history varchar(500)
)

Pour déclarer une variable possédant la même structure que la table "cities", on utilisera :

DECLARE varcity cities%ROWTYPE;

Dans le PL/pgSQL, on pourra ensuite populer la variable à partir d'un SELECT :

SELECT INTO varcity *
FROM cities
WHERE city_id = 100;

Lui attribuer des valeurs :

varcity.city_name := 'Montréal';
varcity.province := 'QC';
varcity.history := 'Voir Wikipedia...';

Et insérer l'enregistrement à partir de la structure :

-- insertion 1 (si city_id est statique)
INSERT INTO cities (city_id, city_name, province)
SELECT varcity.*;

-- insertion 2 (si city_id est auto-increment.)
INSERT INTO cities (city_name, province)
SELECT varcity.city_name, varcity.province;

L'avantage d'utiliser %rowtype est que si on redéfinissait le champ "history" de la table "cities" pour qu'il passe de varchar(500) à un champ "text", il ne serait pas nécessaire d'adapter le code puisque %rowtype redéfinirait automatiquement la variable au moment de l'exécution.