Discussion :

[Pach30]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[alassanediakite]

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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$;

@+

[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 ?

[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 .

[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

[alassanediakite]

Salut

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.
@+

[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.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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 SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Pach30]

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 .