Forums PostgreSQL.fr

sophonie

Membre

Insertion des tuples dans la base avec un code PL/pgSQL.

Bonjour à tous et à toutes,

Actuellement, j'écris mon premier script PL/pgSQL, le but de ce script est le suivant :

-- Ecrire un code PL/pgSQL qui permettra de rajouter 2 personnes
-- un recruteur du département 87
-- un candidat du département 24

La requête s'execute correctement, mais ne renvoie aucun résultat, ce qui est normal.
Par contre lorsque je tape un ordre SQL à la suite du script pour récupérer mes tuples, je
n'en récupère aucun.

a) J'ai crée une procédure, dc elle ne renvoie pas de valeur. Aurait-il fallu créer une fonction?
Mais quelles seraient ses paramètres et le type de retour?

b) Pourquoi ne peut on pas mettre comme type de retour nomdelatable%ROWTYPE pour récupérer
l'ensemble des valeurs de la ligne.

Voici l'ensemble du code :

CREATE OR REPLACE FUNCTION ajout_typp_dept ( ) RETURNS VOID AS $$

DECLARE

v_idauth INTEGER;
v_idtype INTEGER;
v_iddept INTEGER;

v_type typepersonne.typp_libelle%TYPE;
v_dept departements.dept_nom%TYPE;

BEGIN

-- Récupération du type de la personne

v_type := 'Candidat';
SELECT typp_libelle INTO v_type FROM typepersonne WHERE typp_libelle = v_type; -- idem pour Recruteur

-- Récupération du département de la personne

v_dept := '24'; -- idem pour le 87
SELECT dept_nom INTO v_dept FROM departements WHERE dept_nom = v_dept;

-- Ajout dans la table AUTHENTIFICATION

INSERT INTO authentification (auth_id, auth_login, auth_password, auth_dtecreation, dtelastconn, auth_actif)
VALUES ((SELECT COALESCE (MAX(auth_id),0) + 1 FROM authentification), 'transact','PLkHDS3', CURRENT_DATE, CURRENT_DATE, 1)
RETURNING (auth_id) INTO v_idauth;

-- Ajout dans la table PERSONNES

INSERT INTO personnes (pers_id, pers_nom, pers_prenom, pers_dtenaissance, pers_adresse1, pers_adresse2,
pers_cp, pers_ville, pers_telephone, pers_fax, pers_gsm, pers_email, pers_idauth, pers_idtype,pers_iddept)
VALUES ((SELECT COALESCE (MAX(pers_id),0) + 1 FROM personnnes), 'GENIN','Vincent', date(21/12/1984), '31, Route de Treillissac',
'', '24000', 'Treillissac', '0547253210', '', '0652497510', 'v.genin@netscape.net', v_idauth, v_type, v_dept);

COMMIT;
END;

$$LANGUAGE plpgsql;

Merci d'avance, car je ne m'en sort pas.
Sophonie.

Dernière modification par sophonie (17/01/2009 16:13:53)

gleu

Administrateur

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

J'avoue que je ne comprends pas très bien ce que tu cherches à obtenir comme retour. Si tu veux que la procédure stockée renvoie des données, elle doit indiquer le type de données qu'elle renvoie (donc pas void).

Pour la question b, il te suffit d'indiquer le nom de la table. Quelque chose du genre :

CREATE OR REPLACE FUNCTION ajout_typp_dept ( ) RETURNS nomdelatable AS $$...

---

Guillaume.

sophonie

Membre

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

Bonsoir gleu,

Comme je l'ai annoncé dans le ticket initial, ceci est mon premier script PL/pgSQL.

Je me disais bien que quelque chose ne collait pas 'something is wrong!', j'attends un retour alors
que j'ai crée une procédure renvoyant VOID (ie rien!).

Mais que fallait-il mettre comme retour, telle était la question?

En PL/SQL sous Oracle, cet action est beaucoup plus simple car c'est un simple bloc de code sans
le CREATE OR REPLACE FUNCTION ... $$

Merci encore.

Dernière modification par sophonie (17/01/2009 21:43:49)

sophonie

Membre

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

En ajoutant personnes (nomdelatable), il ne m'affiche pas de résultat de retour.
J'ai essayé un personnes%TYPE mais il ne l'accepete pas, d'ailleurs ni un personnes.pers_nom%TYPE.

gleu

Administrateur

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

Pour que la fonction renvoie quelque chose, tu dois utiliser l'instruction RETURN. Voir http://docs.postgresql.fr/8.3/plpgsql-c … tures.html pour plus de détails.

---

Guillaume.

sophonie

Membre

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

Bonjour gleu,

En lisant la documentation de façon plus approfondie (j'avais lu ce chapitre sans vraiment l'appliquer),
j'en arrive à ce code si :

CREATE OR REPLACE FUNCTION ajout_typp_dept() RETURNS SETOF RECORD AS $$

DECLARE

v_idauth INTEGER;
v_idtype INTEGER;
v_iddept INTEGER;

v_type typepersonne.typp_libelle%TYPE;
v_dept departements.dept_nom%TYPE;

pers_rec personnes%ROWTYPE;
auth_rec authentification%ROWTYPE;

BEGIN

-- Récupération du type de la personne

v_type := 'Candidat';
SELECT typp_libelle INTO v_type FROM typepersonne WHERE typp_libelle = v_type; -- idem pour Recruteur

-- Récupération du département de la personne

v_dept := '24'; -- idem pour le 87
SELECT dept_nom INTO v_dept FROM departements WHERE dept_nom = v_dept;

-- Ajout dans la table AUTHENTIFICATION

INSERT INTO authentification (auth_id, auth_login, auth_password, auth_dtecreation, auth_dtelastconn, auth_actif)
VALUES ((SELECT NVL(MAX(auth_id),0) + 1 FROM authentification), 'transact','PLkHDS3', CURRENT_DATE, CURRENT_DATE, 1)
RETURNING (auth_id) INTO v_idauth;

SELECT * INTO auth_rec FROM authentification;
RETURN NEXT auth_rec;

-- Ajout dans la table PERSONNES

INSERT INTO personnes (pers_id, pers_nom, pers_prenom, pers_dtenaissance, pers_adresse1, pers_adresse2,
pers_cp, pers_ville, pers_telephone, pers_fax, pers_gsm, pers_email, pers_idauth, pers_idtype,pers_iddept)
VALUES ((SELECT NVL(MAX(pers_id),0) + 1 FROM personnnes), 'GENIN','Vincent', date(21/12/1984), '31, Route de Treillissac',
'', '', 'Treillissac', '0547253210', '', '0652497510', 'v.genin@netscape.net', v_idauth, v_type, v_dept);

SELECT * INTO pers_rec FROM personnes;
RETURN NEXT pers_rec;

RETURN;

COMMIT;
END;

$$ LANGUAGE 'plpgsql';

SELECT * FROM ajout_typp_dept();

Mais j'ai le message suivant :

ERROR:  a column definition list is required for functions returning "record"

********** Erreur **********

ERROR: a column definition list is required for functions returning "record"
État SQL :42601

gleu

Administrateur

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

C'est dû au fait que le SELECT ne sait pas à quoi s'attendre comme colonnes et types. Tu dois donc les préciser. Quelque chose du genre :

SELECT * FROM ajout_typp_dept() AS t (id int4, c text, ...);

Par contre, je pense que la déclaration de la table authentification ne correspond pas à la déclaration de la table personnes. Donc ça ne fonctionnera pas. Imagine que la première renvoie trois colonnes et l'autres quatre. Combien devrais-tu avoir de colonnes en sortie ? D'autre part, si la colonne 2 renvoie du text dans le premier cas et de l'int dans le second, quel type est-il sensé renvoyé ? Bref, tu ne peux pas renvoyer plusieurs lignes si ces lignes n'ont pas la même définition.

---

Guillaume.

sophonie

Membre

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

Bonsoir gleu,

J'ai modifié mon script (ie j'ai mis en commentaire toutes les informations concernant la table personnes).
Je vais procéder par dichotomie (uniquement la table authentification dans un premier temps)

La fonction ajout_typp_dept() se réfère à l'authentification de la personne avant son insertion dans la table
personnes.

Par contre, lorsque tu dis que la requête :

SELECT * FROM ajout_typp_dept() AS t (id int4, c text, ...); doit être de ce type mais la variable t à quoi correspond-elle, d'une part? et faut-il la déclare dans le bloc DECLARE du script.
Je suppose que c'est une variable de type RECORD, ou de type-composite %ROWTYPE?

Merci encore une fois.

Dernière modification par sophonie (18/01/2009 23:37:33)

gleu

Administrateur

Re : Insertion des tuples dans la base avec un code PL/pgSQL.

Ce n'est pas une variable, c'est un alias. Ce sera ensuite le nom de cette pseudo-table pour le reste de la requête.

---

Guillaume.