Passer dans les paramètres "où" à la vue PostgreSQL?
j'ai une requête assez compliquée sur ma base de données PostgreSQL couvrant 4 tables via une série de sous-séries imbriquées. Cependant, en dépit de l'apparence et de la configuration un peu délicate, il retournera finalement deux colonnes (de la même table, si cela aide la situation) basée sur cette correspondance de deux paramètres externes (deux chaînes doivent correspondre avec des champs dans des tables différentes). Je suis assez nouveau à la conception de base de données dans PostgreSQL, donc je sais que cette chose apparemment magique appelé vues existent, et qui semble comme il pourrait m'aider ici, mais peut-être pas.
y a-t-il un moyen de déplacer ma requête complexe à l'intérieur d'une vue et de lui passer les deux valeurs que je dois faire correspondre? Cela simplifierait grandement mon code sur le front-end (en déplaçant les complexités à la structure de base de données). Je peux créer une vue qui enveloppe ma requête d'exemple statique, et cela fonctionne très bien, cependant cela ne fonctionne que pour une paire de valeurs de chaîne. Je dois pouvoir l'utiliser avec une variété de des valeurs différentes.
ainsi ma question Est: est-il possible de passer des paramètres dans une vue autrement statique et est-il devenu "dynamique"? Ou peut-être une Vue n'est pas la bonne façon de l'aborder. S'il y a autre chose qui marcherait mieux, je suis tout ouïe!
*Edit:* Comme l'a demandé dans les commentaires, voici ma requête actuelle:
SELECT param_label, param_graphics_label
FROM parameters
WHERE param_id IN
(SELECT param_id
FROM parameter_links
WHERE region_id =
(SELECT region_id
FROM regions
WHERE region_label = '%PARAMETER 1%' AND model_id =
(SELECT model_id FROM models WHERE model_label = '%PARAMETER 2%')
)
) AND active = 'TRUE'
ORDER BY param_graphics_label;
les paramètres sont activés par les symboles de pourcentage ci-dessus.
5 réponses
vous pouvez utiliser une fonction de retour set:
create or replace function label_params(parm1 text, parm2 text)
returns table (param_label text, param_graphics_label text)
as
$body$
select ...
WHERE region_label =
AND model_id = (SELECT model_id FROM models WHERE model_label = )
....
$body$
language sql;
Ensuite, vous pouvez faire:
select *
from label_params('foo', 'bar')
Btw: êtes-vous sûr que vous voulez:
AND model_id = (SELECT model_id FROM models WHERE model_label = )
si model_label n'est pas unique (ou la clé primaire) alors cela finira par lancer une erreur. Vous voulez probablement:
AND model_id IN (SELECT model_id FROM models WHERE model_label = )
en plus de ce que @a_horse a déjà éclairci, vous pouvez simplifier votre SQL en utilisant la syntaxe de JOINTURE au lieu de sous-séries imbriquées. Les performances seront similaires, mais la syntaxe est beaucoup plus courte et plus facile à gérer.
CREATE OR REPLACE FUNCTION param_labels(_region_label text, _model_label text)
RETURNS TABLE (param_label text, param_graphics_label text) AS
$func$
SELECT p.param_label, p.param_graphics_label
FROM parameters p
JOIN parameter_links l USING (param_id)
JOIN regions r USING (region_id)
JOIN models m USING (model_id)
WHERE p.active
AND r.region_label =
AND m.model_label =
ORDER BY p.param_graphics_label;
$func$ LANGUAGE sql;
Si
model_labeln'est pas unique ou quelque chose d'autre dans la requête crée des lignes en double, vous pouvez faire queSELECT DISTINCT p.param_graphics_label, p.param_label- avec un correspondantORDER BYclause pour la meilleure performance. Ou utiliser unGROUP BYclause.depuis Postgres 9.2 vous pouvez utiliser les noms de paramètres déclarés à la place de
etdans les fonctions SQL. (A été possible pour les fonctions PL/pgSQL pendant une longue période).il faut prendre soin d'éviter les conflits de noms. C'est pourquoi j'ai pris l'habitude de préfixer les noms de paramètres dans la déclaration (ceux-ci sont visibles le plus partout à l'intérieur de la fonction) et les noms de colonnes dans la table-qualify corps.
j'ai simplifié
WHERE p.active = 'TRUE'WHERE p.active, parce que la colonneactivedevrait probablement être de typeboolean, pastext.USINGne fonctionne que si les noms de colonnes ne sont pas ambigus dans toutes les tables situées à gauche de la jointure. Sinon, vous devez utiliser la syntaxe plus explicite:ON l.param_id = p.param_id
dans la plupart des cas, la fonction set-returning est le chemin à suivre, mais dans le cas où vous voulez à la fois lire et écrire sur le set, une vue peut être plus appropriée. Et il possibilité pour une vue de lire un paramètre de session:
CREATE VIEW widget_sb AS SELECT * FROM widget WHERE column = cast(current_setting('mydomain.myparam') as int)
SET mydomain.myparam = 0
select * from widget_sb
[results]
SET mydomain.myparam = 1
select * from widget_sb
[distinct results]
Je ne pense pas qu'une vue" dynamique " comme vous l'avez dit soit possible.
Pourquoi ne pas écrire une procédure stockée qui prend 2 arguments à la place?
je reformule la requête comme suit:
SELECT p.param_label, p.param_graphics_label
FROM parameters p
where exists (
select 1
from parameter_links pl
where pl.parameter_id = p.id
and exists (select 1 from regions r where r.region_id = pl.region_id
) and p.active = 'TRUE'
order by p.param_graphics_label;
en supposant que vous avez des index sur les différentes colonnes d'id, cette requête devrait être significativement plus rapide que l'utilisation de l'opérateur IN; Les paramètres existants ici n'utiliseront que les valeurs d'index sans même toucher la table de données, sauf pour obtenir les données finales de la table de paramètres.