Forums PostgreSQL.fr

lo²

Membre

[Débutant][8.2] Aide syntaxe Fonction SQL

Bonjour à tous,

Je me trouve devant un problème de syntaxe sur ma création de procédure. Visiblement, le problème viendrait du DECLARE non reconnu en LANGUAGE SQL mais ça me surprend.

Voici le code :

CREATE FUNCTION P0600 (
IN V0 SMALLINT
, IN V1 CHAR(5)
, IN V2 CHAR(5)
, IN V3 CHAR(8)
, IN V4 CHAR(8)
) RETURNS refcursor as
$$
DECLARE 
ref refcursor;
I1 VARCHAR(5000);
BEGIN
SET I1 = 'SELECT F0600.F0600_00, F0600_01, F0600_02, F0600_03, F0600_04, F0600_05, F0600_06, F0600_08, F0600_11, F0600_12,
	F0601.F0601_00, F0601.F0602_00, F0601.F0603_00, F0601.F0606_00, F0600_13, F0600_16, F0600_17, F0600_18, F0600.F0603_00, F0600.F0606_00,
	F0600_19, F0600_20, F0600_21, F0600_22, F0600_23, F0600_24
	FROM F0600 AS F0600
	LEFT OUTER JOIN F0609 AS F0609 ON F0609.F0600_00 = F0600.F0600_00
	LEFT OUTER JOIN F0601 AS F0601 ON F0601.F0601_00 = F0609.F0601_00
	WHERE F0600.F0001_00 =  ''' || V1 || ''' AND F0600.F0002_00 = ''' || V2 || '''';
IF V0 = 1 THEN 
	SET I1 = I1 || ' AND ((F0601_03 >= ' || V3 || ' AND F0601_03 < ' || V4 || ')
	AND F0601.F0601_00 IS NOT NULL AND F0601.F0602_00 IS NOT NULL AND F0601.F0603_00 IS NOT NULL AND F0601.F0606_00 IS NOT NULL)';
ELSE
	SET I1 = I1 || ' AND (F0601.F0601_00 IS NULL OR F0601.F0602_00 IS NULL OR F0601.F0603_00 IS NULL OR F0601.F0606_00 IS NULL)';
END IF;
OPEN ref FOR I1;
RETURN ref;
END;
$$
LANGUAGE 'SQL';

Voici le message d'erreur :

ERROR:  syntax error at or near "refcursor" at character 152

Merci pour votre aide

Dernière modification par lo² (01/07/2009 10:38:23)

Marc Cousin

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Ce n'est pas du SQL mais du plpgsql => Changer la directive LANGUAGE à la fin, pour commencer. A mon avis, le problème principal vient de là (je ne garantis pas qu'il n'y a pas d'autre erreur de syntaxe )

---

Marc.

Marc Cousin

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Ensuite l'affectation en plpgsql ne se fait pas avec SET, mais avec l'opérateur :=
dont pas SET I1 = I1 ...
mais I1 := I1 ....

---

Marc.

lo²

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Merci pour cette réponse rapide.

La procédure qui est en exemple provient d'un projet existant qui tourne sur Iseries. Il existe une trentaine de procédures stockées.
J'ai regardé la syntaxe du langage plpgsql mais je trouvais qu'il y avait pas mal de diiférence dans la syntaxe, dans le sens où j'aimerais que lors de la mise en place du projet, le code de création de base (tables, procedures...) soit le plus uniforme possible.

Est-il possible en utilisant la syntaxe SQL d'arriver à ce type de procédure ou faut-il obligatoirement passer par plpgsql ?

lo²

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Visiblement tout le monde parle en plpgsql et en grattant un peu plus, il se pourrait qu'il n'y ait pas tant de modifs à apporter que ça.

Voici le code en pgplsql :

CREATE FUNCTION P0600 (
IN V0 SMALLINT
, IN V1 CHARACTER(5)
, IN V2 CHARACTER(5)
, IN V3 CHARACTER(8)
, IN V4 CHARACTER(8)
)
RETURNS refcursor AS
$$
DECLARE 
rec refcursor;
I1 VARCHAR(5000);
begin
I1 := 'SELECT F0600.F0600_00, F0600_01, F0600_02, F0600_03, F0600_04, F0600_05, F0600_06, F0600_08, F0600_11, F0600_12,
F0601.F0601_00, F0601.F0602_00, F0601.F0603_00, F0601.F0606_00, F0600_13, F0600_16, F0600_17, F0600_18, F0600.F0603_00, F0600.F0606_00,
F0600_19, F0600_20, F0600_21, F0600_22, F0600_23, F0600_24
FROM F0600 AS F0600
LEFT OUTER JOIN F0609 AS F0609 ON F0609.F0600_00 = F0600.F0600_00
LEFT OUTER JOIN F0601 AS F0601 ON F0601.F0601_00 = F0609.F0601_00
WHERE F0600.F0001_00 =  ''' || V1 || ''' AND F0600.F0002_00 = ''' || V2 || '''';
IF (V0 = 1) THEN
I1 := I1 || ' AND ((F0601_03 >= ' || V3 || ' AND F0601_03 < ' || V4 || ')
AND F0601.F0601_00 IS NOT NULL AND F0601.F0602_00 IS NOT NULL AND F0601.F0603_00 IS NOT NULL AND F0601.F0606_00 IS NOT NULL)';
ELSE
I1 := I1 || ' AND (F0601.F0601_00 IS NULL OR F0601.F0602_00 IS NULL OR F0601.F0603_00 IS NULL OR F0601.F0606_00 IS NULL)';
END IF;
OPEN ref FOR EXECUTE I1;
RETURN ref;
END;
$$
LANGUAGE 'plpgsql';

et le message d'erreur (au niveau de OPEN ref):

ERROR:  syntax error at or near "ref" at character 1105

Marc Cousin

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

en fait l'erreur est beaucoup plus haut :
DECLARE rec refcursor;
devrait être :
DECLARE ref refcursor;

---

Marc.

gleu

Administrateur

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Est-il possible en utilisant la syntaxe SQL d'arriver à ce type de procédure ou faut-il obligatoirement passer par plpgsql ?

Si vous voulez utiliser des structures de contrôle (if/then/else, case en 8.4, etc) ou si vous voulez utiliser des structures de boucle (FOR, WHILE et autres), vous ne pouvez pas utiliser SQL. Vous devez passer par un langage plus évolué comme PL/pgsql, PL/perl, PL/python, etc.

---

Guillaume.

lo²

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

rooh l'erreur de newbie ^^ et merci pour la précision à propos des limites d'utilisation de SQL.

Maintenant j'aimerai récupérer l'ensemble des enregistrements retournée par la procédure avec un :

select P0600(0, '00000', '00000', '20090601', '20090630');

mais voici le message d'erreur qui en résulte :

ERROR:  function p0600(integer, unknown, unknown, unknown, unknown) does not exist at character 8
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Visiblement, il semblerait que cela provienne des paramètres de la procédure qui sont en CHARACTER et convertis en BPCHAR une fois créée dans la base.

Marc Cousin

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

C'est le typage des paramètres de la fonction qui posent problème :
- le smallint, il vaudrait mieux le passer en int (comme ca postgresql pourra caster à la volée)
- les paramètres suivants sont de type varchar, pas  char (plus d'un caractère)

---

Marc.

lo²

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

En effet, ça passe beaucoup mieux.

Par contre, le résultat me donne un <unnamed portal 1>.
Quelle est la modification à apporter dans ma fonction pour un avoir le même résultat qu'une requête exécutée simplement ?

lo²

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Ca y est, j'ai le résultat escompté mais faut-il forcément passer par la création d'un type reprenant l'ensemble des champs remontés par la procédure ?

CREATE FUNCTION P0600 (
IN V0 INTEGER
, IN V1 VARCHAR(5)
, IN V2 VARCHAR(5)
, IN V3 VARCHAR(8)
, IN V4 VARCHAR(8)
)
RETURNS setof p0600_t AS
$$
DECLARE 
rec p0600_t%rowtype;
I1 VARCHAR(5000);
begin
I1 := 'SELECT F0600.F0600_00, F0600_01, F0600_02, F0600_03, F0600_04, F0600_05, F0600_06, F0600_08, F0600_11, F0600_12,
F0601.F0601_00, F0601.F0602_00, F0601.F0603_00, F0601.F0606_00, F0600_13, F0600_16, F0600_17, F0600_18, F0600.F0603_00, F0600.F0606_00,
F0600_19, F0600_20, F0600_21, F0600_22, F0600_23, F0600_24
FROM F0600 AS F0600
LEFT OUTER JOIN F0609 AS F0609 ON F0609.F0600_00 = F0600.F0600_00
LEFT OUTER JOIN F0601 AS F0601 ON F0601.F0601_00 = F0609.F0601_00
WHERE F0600.F0001_00 =  ''' || V1 || ''' AND F0600.F0002_00 = ''' || V2 || '''';
IF (V0 = 1) THEN
I1 := I1 || ' AND ((F0601_03 >= ' || V3 || ' AND F0601_03 < ' || V4 || ')
AND F0601.F0601_00 IS NOT NULL AND F0601.F0602_00 IS NOT NULL AND F0601.F0603_00 IS NOT NULL AND F0601.F0606_00 IS NOT NULL)';
ELSE
I1 := I1 || ' AND (F0601.F0601_00 IS NULL OR F0601.F0602_00 IS NULL OR F0601.F0603_00 IS NULL OR F0601.F0606_00 IS NULL)';
END IF;
FOR rec IN EXECUTE I1
LOOP
RETURN NEXT rec;
END LOOP; 
END;
$$
LANGUAGE 'plpgsql';

gleu

Administrateur

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Non.

Vous pouvez les décrire avec des paramètres OUT (mais bon, ça vous en fait plus de 20 à déclarer pour chaque fonction sensée renvoyer ce type de record).

Si F0600.F0600_00, F0600_01, F0600_02, F0600_03, F0600_04, F0600_05, F0600_06, F0600_08, F0600_11, F0600_12, F0601.F0601_00, F0601.F0602_00, F0601.F0603_00, F0601.F0606_00, F0600_13, F0600_16, F0600_17, F0600_18, F0600.F0603_00, F0600.F0606_00, F0600_19, F0600_20, F0600_21, F0600_22, F0600_23, F0600_24 correspondent à l'ensemble des colonnes de la table F0600, vous pouvez utiliser le type du même nom que la table, ce qui donnerait un « RETURNS SETOF F0600 ».

Enfin, vous pouvez aussi renvoyer le type RECORD, mais il vous faudra indiquer les colonnes dans la requête qui appelle la fonction.

---

Guillaume.

lo²

Membre

Re : [Débutant][8.2] Aide syntaxe Fonction SQL

Merci beaucoup pour tous ces éclaircissements.

Bonne journée
lo²