Paramétre de procédure

Version imprimable

Bonjour

je n'ai aucune maitrise de postgresql que je découvre pour les besoins d'un projet
j'ai une procédure qui plante à l'exécution et malgré la lecture approndie et attentive de l'excellent livre "SQL" de F.Brouard & C.Soutou, je ne comprends pas pourquoi
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE PROCEDURE public.cree_t_experience(IN _numpheno integer DEFAULT 1) LANGUAGE 'sql' AS $BODY$ Drop view if exists config_pheno; create view config_pheno as ( select ps.Name as camera , pp.description as Pheno FROM "Pheno_sensor" ps ,"Pheno_phenoscope" pp where pp.id = ps.default_phenoscope_id and pp.id =_numpheno ) ;
le retour fait par postgres est :
ERREUR: la colonne « _numpheno » n'existe pas
LINE 7: ...ere pp.id = ps.default_phenoscope_id and pp.id =_numpheno...
^
Pourtant quand j'écris un integer en dur, la procédure s'exécute sans souci
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE PROCEDURE public.cree_t_experience(IN _numpheno integer DEFAULT 1) LANGUAGE 'sql' AS $BODY$ Drop view if exists config_pheno; create view config_pheno as ( select ps.Name as camera , pp.description as Pheno FROM "Pheno_sensor" ps ,"Pheno_phenoscope" pp where pp.id = ps.default_phenoscope_id and pp.id =2 ) ;
j'ai essayé différentes syntaxes sans succès :
:_numpheno
:'_numpheno'
?
nada ! tjs à me dire que la colonne (?) n'existe pas !!

Merci d'avance à ceux qui répondront.

Salut
En effet c'est vraiment étrange.
Il me semble que le problème vient de l'ordre "create view..."
Le même problème existe en plpgsql que ce soit avec une fonction ou une procédure. L'utilisation $1 (référence de paramètre) donne l'erreur "Il n'y a pas de paramètre $1"
Il faut essayer en plpgsql de cette manière...
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE OR REPLACE PROCEDURE public.cree_t_experience( _numpheno integer DEFAULT 1) LANGUAGE 'plpgsql' AS $BODY$ begin Drop view if exists config_pheno; EXECUTE 'select ps.Name as camera , pp.description as Pheno FROM "Pheno_sensor" ps ,"Pheno_phenoscope" pp where pp.id = ps.default_phenoscope_id and pp.id =' || _numpheno; end; $BODY$;
@+

17/05/2021, 09h54
escartefigue

bonjour,

Les quotes simples ne sont pas utilisables : elles servent à délimiter les chaînes de caractères.
Pour les noms d'objet (de tables, de colonnes...), ce sont les guillemets ou double quotes qu'il faut utiliser.

Mais pourquoi ne pas tout simplement créer la vue une bonne fois pour toutes hors procédure ?
17/05/2021, 10h38
Pach30

Bonjour

Merci pour vos réponses .
Malheureusement, simple quote ou guillemet ne change rien au comportement : Postgre continu à me dire que la colonne n'existe pas . Quand à la raison du paramétre , c'est que j'ai plusieurs machines numérotées de 1 à x dont la configuration peut évoluer dans le temps et dans la supervision , ces machines sont des instances d'un objet .
17/05/2021, 10h54
escartefigue

Je vois qu'il manque l'alias de table dans la clause WHERE devant _numpheno

Profitez de la modif pour coder les jointures avec l'opérateur JOIN, c'est quand même plus propre ;)
17/05/2021, 19h07
alassanediakite

Salut

Citation:

Envoyé par escartefigue

Je vois qu'il manque l'alias de table dans la clause WHERE devant _numpheno

_numpheno n'est pas une colonne d'une table, c'est un paramètre de la procédure.
Certes pour l'erreur, PostgreSQL emplois "colonne" mais il s'agit bien d'un paramètre! d'où le titre de la discussion.
@+

Une vue est un objet (ou requête) statique, elle n'accepte pas d'expressions contenant des paramètres : numpheno_ ou $1
Pour faire la même chose, passez par une fonction qui retourne une table comme résultat de requête.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE OR REPLACE FUNCTION config_pheno( numpheno_ integer DEFAULT 1)
RETURNS table (camera varchar, pheno varchar)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
 
	RETURN QUERY
		SELECT ps.name, pp.description
		FROM pheno_sensor ps
		INNER JOIN pheno_phenoscope pp ON pp.id = ps.default_phenoscope_id
		WHERE pp.id = numpheno_;
 
END;
$BODY$;

Puis l'utiliser comme une table :

Code:

1
2
3
4
 
SELECT camera, pheno FROM config_pheno();
SELECT camera, pheno FROM config_pheno(14);
SELECT t.camera, t.pheno FROM config_pheno(2) t;

Sinon créez votre vue sans passer par une fonction :

Code:

1
2
3
4
5
 
CREATE VIEW config_pheno AS
SELECT pp.id, ps.name, pp.description
FROM pheno_sensor ps
INNER JOIN pheno_phenoscope pp ON pp.id = ps.default_phenoscope_id;

Et l'utiliser simplement comme ceci:

Code:

1
2
3
 
SELECT camera, pheno FROM config_pheno;
SELECT camera, pheno FROM config_pheno WHERE id = 1;

18/05/2021, 22h58
Pach30

Citation:

Envoyé par manzeki

Une vue est un objet (ou requête) statique, elle n'accepte pas d'expressions contenant des paramètres : numpheno_ ou $1
Pour faire la même chose, passez par une fonction qui retourne une table comme résultat de requête.

Merci Manzeki pour cette explication convaincante .