Vous n'êtes pas identifié(e).
Dans ma boite, on a un gitlab interne pour tous les projets.
Donc si je comprends bien, il suffit de créer un projet spécifique à mes fonctions plpgsql, de travailler sur dans ce projet.
une fois que j'estime ma fonction ok, c'est le script shell qui va faire un drop / create du fichier qui m'intéresse
la solution me parait pas mal, faut voir la mise en place
Ma question est plutôt "comment intégrer git" à une base de données postgres pour version les fonctions.
Effectivement git est un outil formidable, que j'utilise aussi au quotidien !
Bonjour,
Je suis en train de développer un certain nombre de fonctions en plpgsql.
Existe-t-il une solution pour versionner le code de type git ?
L'idée derrière ça est d'assurer une traçabilité des modifications (évolution/correction de bug), et de permettre un éventuel retour en arrière .
merci d'avance
super merci beaucoup !!
En fait je ne comprends pas comment utliser le
FETCH ALL FROM :"curseur"; j'utilise DBeaver comme outil pour faire mes requêtes et voici le retour que j'ai :
appel à la fonction :
select dynamic_pivot(
'select id_echantillon,
id_type_analyse ,
max(a.valeur) valeur
from t_analyse a where a.created >''2024-02-01''
and a.valeur IS NOT null and a."etat" = ''valide''
group by id_echantillon, id_type_analyse',
'select id_type_analyse from t_type_analyse') as curseurcurseur |
------------------+
<unnamed portal 3>|Ensuite quand j'exécute
FETCH ALL FROM curseur; j'ai une erreur
SQL Error [34000]: ERROR: cursor "curseur" does not existj'ai essayé aussi
FETCH ALL FROM "<unnamed portal 3>" mais l'erreur est la même
je connais cette extension mais qui répond pas aux attentes à savoir que le nombre et le nom des colonnes doivent être connus. Et c'est là tout le problème....c'est que je ne le sais pas à l'avance
Bonjour,
Je cherche une solution pour reproduire un pivot dynamique.
J'ai trouvé une fonction qui utilise `row_to_json`(https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html), mais je ne comprends pas la mise en pratique.
La fonction :
CREATE FUNCTION dynamic_pivot(central_query text, headers_query text)
RETURNS refcursor AS
$$
DECLARE
left_column text;
header_column text;
value_column text;
h_value text;
headers_clause text;
query text;
j json;
r record;
curs refcursor;
i int:=1;
BEGIN
-- find the column names of the source query
EXECUTE 'select row_to_json(_r.*) from (' || central_query || ') AS _r' into j;
FOR r in SELECT * FROM json_each_text(j)
LOOP
IF (i=1) THEN left_column := r.key;
ELSEIF (i=2) THEN header_column := r.key;
ELSEIF (i=3) THEN value_column := r.key;
END IF;
i := i+1;
END LOOP;
-- build the dynamic transposition query (based on the canonical model)
FOR h_value in EXECUTE headers_query
LOOP
headers_clause := concat(headers_clause,
format(chr(10)||',min(case when %I=%L then %I::text end) as %I',
header_column,
h_value,
value_column,
h_value ));
END LOOP;
query := format('SELECT %I %s FROM (select *,row_number() over() as rn from (%s) AS _c) as _d GROUP BY %I order by min(rn)',
left_column,
headers_clause,
central_query,
left_column);
-- open the cursor so the caller can FETCH right away
OPEN curs FOR execute query;
RETURN curs;
END
$$ LANGUAGE plpgsql;je voudrais qu' à l'utilisation cette requête
select dynamic_pivot(
'select id_echantillon,
id_type_analyse ,
max(a.valeur) valeur
from t_analyse" a where a.created >''2024-02-01''
and a.valeur IS NOT null and a."etat" = ''valide''
group by id_echantillon, id_type_analyse',
'select id_type_analyse from t_type_analyse') as cur;si j'ai un jeu de données renvoyé par la première partie de la fonction comme celui ci:
id_echantillon id_type_analyse valeur
1471575 52 20.8
1471575 53 46.4
1471575 54 0.02
1471575 55 0
1471576 1 75.8
1471576 2 59.2
1471576 3 532000
1471576 4 606il me faudrait un tableau final comme cela :
id_echantillon 1 2 3 4 52 53 54 55
141575 20.8 46.4 0.02 0
147576 75.8 59.2 532000 606 C'est très difficile de vous répondre vu qu'on ne connaît pratiquement rien du contexte. Avec le peu d'infos qu'on a, je dirais de stocker quand même les dates. Ça ne coute pas grand chose et ça permet d'utiliser un vrai type date et les fonctions associées. Voire un daterange, qui serait certainement plus pratique pour vous.
oh que je suis d'accord !!
par expérience, si vous avez des dates à gérer utilisez le bon type (date)
j'utilise au quotidien une (vieille) base (oracle) avec des dates séparées en 3 champs (année, mois jour)
je vous laisse imaginer quand vous rajoutez une journée au 31/12/2018........et bien on est le 32/12/2018!
et si vous retranchez 5 jours au 03/02/2018......c'est la misère
la solution est le typage à la volée dans les requêtes mais les perf s'effondrent, et oracle en plus gère assez mal ça dans le prédicat
alors qu'avec un type date et une base postgres tout cela se fait naturellement sans maux de tête !
Le plus pénible à installer dans l'histoire c'est l'instant client Oracle !!
Mais une fois que c'est fait, ça fonctionne bien.
Bon courage !
Bonjour,
J'avais fait un post sur ce sujet il y a quelques temps : https://forums.postgresql.fr/viewtopic.php?id=4122
ainsi qu'un petit "tuto" récapitulatif de l'installation : https://www.developpez.net/forums/blogs … posgresql/
si cela peut vous aider.....oracle est tellement galère à installer
Par curiosité, quelle est le réel intérêt de stoquer les données ainsi dans un champs json, la modèlisation ne pourrait être autrement ?
sais pas !!! dans ce cas je ne suis qu'utilisateur !!
Yes merci
c'est un peu sportif cette histoire, voilà le code final pour extraire tous les éléments
select client , json_array_elements_text(json_array_elements(champjson::json)->'d')::int as pb FROM matablepar contre il faut "retyper" le jsonb en json sinon ça ne fonctionne pas
Bonjour,
je dois faire une requête sur une table dont un des champs est en jsonb avec un stockage du style :
client, champjson
1234, [{"a": "", "b": "", "c": "", "d": [19, 38, 34, 13], "e": ""}]
4321,[{"a": "", "b": "", "c": "", "d": [36], "e": ""}]je souhaite que le résultat de ma requête soit ainsi :
1234,19
1234,38
1234,34
1234,13
4321,36j'ai donc tenté une requête comme ceci :
select client, champjson->'d' from matableet j'obtien
1234,NULL
4321,NULLmerci de votre aide
Il semblerait que ce soit natif : https://raw.githubusercontent.com/laure … oracle_fdw
Joins between foreign tables
----------------------------
From PostgreSQL 9.6 on, oracle_fdw can push down joins to the Oracle server,
that is, a join between two foreign tables will lead to a single Oracle query
that performs the join on the Oracle side.
There are some restrictions when this can happen:
- Both tables must be defined on the same foreign server.
- The join must be an inner join.
- Joins between three or more tables won't be pushed down.
- The join must be in a SELECT statement.
- oracle_fdw must be able to push down all join conditions and WHERE clauses.
- Cross joins without join conditions are not pushed down.
- If a join is pushed down, ORDER BY clauses will not be pushed down.
It is important that table statistics for both foreign tables have been
collected with ANALYZE for PostgreSQL to determine the best join strategy.
Bonjour
Effectivement, je viens de me le faire, c'est top !!
je n'avais pas envisager ça au moment où j'avais commencé ce post (1 an déjà ....), je n'avais besoin que de quelques tables oracle.
ce qui me manque toujours c'est les clefs et les index, sur des select simple (1 table ça va à peu près), mais dès qu'on passe pas des jointures, les durées d'exécution sont catastrophiques.
Comme vous êtes sur une version plus récente, y a t il eu des améliorations sur ce point ?
si je pondère ma moyenne finale j'obtiens la même chose qu'en R : 10.873 (c'est moche et tordu.....)
with b as (
select sum(pond) as total from test
),
c as (
select prix, round(pond / total,2) as pond2 from test, b),
d as (
select prix, pond2,
sum(pond2) over (partition by 1 order by prix range between unbounded preceding and current row) as cumul_asc
from c),
e as (
select prix, pond2,
sum(pond2) over (partition by 1 order by prix desc range between unbounded preceding and current row) as cumul_desc
from c),
f as (
select max(d.prix) as resultat from d where cumul_asc <=.5
union
select min(e.prix) from e where cumul_desc <=.5)
select sum(resultat*pond)/sum(pond) from f,test where resultat=prixd'après ce que j'ai pu comprendre et vérifier par le calcul, R recherche les "limites" encadrantes et calcule la moyenne pondérée de ces limites :
prix pondération
10.606 5.554
11 11.705en calculant la moyenne pondérée on tombe sur 10.873
(10.606*5.554+11*11.705)/(5.554+11.705)
j'étais parti sur ce genre de chose avec les fonctions de fenêtrage mais en découpant beaucoup (trop) les requêtes, en ramenant les pondération à 1...... et j'obtiens 10.80
je vous mets quand même ma requête
with b as (
select sum(pond) as total from test),
c as (
select prix, round(pond / total,2) as pond2 from test, b),
d as (
select prix, pond2,
sum(pond2) over (partition by 1 order by prix range between unbounded preceding and current row) as cumul_asc
from c),
e as (
select prix, pond2,
sum(pond2) over (partition by 1 order by prix desc range between unbounded preceding and current row) as cumul_desc
from c),
f as (
select max(d.prix) as resultat from d where cumul_asc <=.5
union
select min(e.prix) from e where cumul_desc <=.5)
select avg(resultat) from favec la solution de dverite, on obtient 10.94
En R, avec la fonction weighted.median de la library spatstat, avec le jeu de données fourni j'obtiens 10.87
je ne connaissais pas la médiane pondérée, merci donc à wikipedia : https://en.wikipedia.org/wiki/Weighted_median#Examples
ça n'a pas l'air simple comme ça....
Il semble que des logiciel de stats tel R propose ce genre de fonction (weight.median) (une extension existe pour postgres)
Je ne pense pas que l'on puisse simplement trouver la médiane pondérée sans passer par un certain nb d'étape intermédiaire ou un prog en pg/sql.
Néanmoins, rien n'étant impossible, qu'attendez-vous comme résultat final avec le jeu de données précédent ?
Un exemple chiffré pourrait permettre de mieux y voir clair, mais vu la requête, je pense que vous retombez direct sur la valeur de votre variable, elle n'est donc pas pondérée.
Faites une mediane sur vos valeurs non pondérées pour voir.......
Bizarre également la fonction median........ce n'est pas implémenté tel que dans postgresql (il faut ça SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY champ) FROM TABLE pour avoir la mediane)
Je me réponds après quelques essais !!
J'avais comme l'intuition que LATERAL pourrait venir à mon secours....
SELECT a.ech, lat.lot, a.ana, a.ech_dh,lat.lot_dh FROM ech a,
LATERAL (SELECT * FROM lot b WHERE b.ana = a.ana AND a.ech_dh>=b.lot_dh ORDER BY lot_dh DESC LIMIT 1) latJ'ai toujours du mal à appréhender la logique de LATERAL....si vous avez un bon tuto par là, je suis preneur !
Bonjour,
Dans le cadre d'une gestion de stock et d'analyses, j'ai les 2 tables suivantes (simplifiées pour l'exemple):
CREATE TABLE public.lot (
lot varchar NULL, --numero de lot
ana varchar NULL, --analyse realise avec le lot
lot_dh timestamp NULL --date d'activation du lot
)
INSERT INTO public.lot (lot,ana,lot_dh) VALUES
('lot1','ST','2017-12-31 08:03:54.000')
,('lot2','C','2018-01-05 12:12:54.000')
,('lot3','FT','2018-01-10 12:03:54.000')
,('lot5','C','2018-01-15 15:15:54.000')
,('lot6','FT','2018-01-14 11:13:14.000')
,('lot4','ST','2018-01-02 18:03:54.000')
CREATE TABLE public.ech (
ana varchar NULL, --analyse
ech_dh timestamp NULL, --date/heure de realisation de l'analyse
ech int4 NULL --numero echantillon
)
INSERT INTO public.ech (ana,ech_dh,ech) VALUES
('ST','2017-12-31 08:02:54.000',1)
,('C','2018-01-05 12:12:59.000',2)
,('FT','2018-01-05 12:12:59.000',2)
,('ST','2018-01-15 17:12:59.000',3)
,('FT','2018-01-15 17:12:59.000',3)
,('C','2018-01-15 17:12:59.000',3)Le but est de retrouver le lot utilisé pour l'échantillon pour telle ou telle analyse.
Par exemple, on sait donc que le lot4 servira à faire l'analyse ST, et qu'il est actif à partir du 02/01/2017 18:03:54
Donc, tant qu'un nouveau lot réalisant cette analyse n'est pas activé, les échantillons seront faits avec le lot 4.
Pour que l'utilisateur puisse obtenir une traçabilité je pensais donc à cette requête avec une reconstitution d'intervalle de date, et qui permet de retourner les échantillons avec les lots utilisés
WITH a AS(
SELECT
lot,
ana,
lot_dh AS date_val_deb,
lead(
lot_dh,
1
) OVER(PARTITION BY ana ORDER BYlot_dh ASC) AS date_val_fin
FROM
lot
ORDER BY
ana,
lot
),
b AS(
SELECT
lot,
ana,
CASE
WHEN date_val_deb IS NULL THEN '(,' || date_val_fin || ')'
WHEN date_val_fin IS NULL THEN '[' || date_val_deb || ',)'
ELSE '[' || date_val_deb || ',' || date_val_fin || ')'
END::tsrange AS plage_val_lot
FROM
a
) SELECT
ech,
lot,
ech.ana,
ech_dh,
plage_val_lot
FROM
ech
LEFT JOIN b ON
ech.ana = b.ana
WHERE
ech_dh < @ plage_val_lot = TRUEce qui me donne
ech |lot |ana |ech_dh |plage_val_lot |
----|-----|----|--------------------|----------------------------------------------|
2 |lot2 |C |2018-01-05 12:12:59 |["2018-01-05 12:12:54","2018-01-15 15:15:54") |
3 |lot5 |C |2018-01-15 17:12:59 |["2018-01-15 15:15:54",) |
3 |lot6 |FT |2018-01-15 17:12:59 |["2018-01-14 11:13:14",) |
3 |lot4 |ST |2018-01-15 17:12:59 |["2018-01-02 18:03:54",) |Y aurait il plus simple ? plus efficace ?
Merci d'avance
Vos 2 tables semblant identiques, ne faudrait-il pas revoir le modèle :
U
Date
Type (budget ou depense)
Montant
?
Ensuite avec une fonction de fenêtrage, on doit pouvoir travailler facilement sur chaque partition (U)
Bonjour,
Plutôt qu'une question, ce post est un partage d'expérience, la doc étant sans exemple sur ce sujet
En faisant des recherches sur les fonctions de fenêtrage, je suis tombé sur cet article
http://sqlpro.developpez.com/article/ol … dow/#LII-C
Pour un calcul de moyenne cumulée, c'est parfait :
select sta_id, val_date,val_esp, val_valeur,
avg(val_valeur) over (partition by sta_id, val_esp order by val_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as moy_cumulée
from tbl_valeur_val
where val_date > '05/12/2017'Ici je calcule pour chaque champ sta_id et val_esp la moyenne cumulée par date croissante
sta_id |val_date |val_esp |val_valeur |moy_cumulée |
-------|-----------|--------|-----------|-----------------------|
1 |2017-12-06 |1 |741.000 |741.0000000000000000 |
1 |2017-12-07 |1 |671.000 |706.0000000000000000 |
1 |2017-12-08 |1 |404.000 |605.3333333333333333 |
1 |2017-12-09 |1 |252.000 |517.0000000000000000 |
1 |2017-12-12 |1 |1.000 |413.8000000000000000 |
1 |2017-12-13 |1 |7.000 |346.0000000000000000 |
1 |2017-12-06 |2 |395.000 |395.0000000000000000 |
1 |2017-12-07 |2 |287.000 |341.0000000000000000 |
1 |2017-12-08 |2 |42.000 |241.3333333333333333 |
1 |2017-12-09 |2 |89.000 |203.2500000000000000 |
1 |2017-12-12 |2 |115.000 |185.6000000000000000 |
1 |2017-12-13 |2 |131.000 |176.5000000000000000 |
2 |2017-12-06 |1 |15.000 |15.0000000000000000 |
2 |2017-12-07 |1 |20.000 |17.5000000000000000 |
2 |2017-12-08 |1 |19.000 |18.0000000000000000 |
2 |2017-12-09 |1 |17.000 |17.7500000000000000 |
2 |2017-12-12 |1 |40.000 |22.2000000000000000 |
2 |2017-12-13 |1 |158.000 |44.8333333333333333 |
2 |2017-12-06 |2 |31.000 |31.0000000000000000 |
2 |2017-12-07 |2 |34.000 |32.5000000000000000 |
2 |2017-12-08 |2 |45.000 |36.6666666666666667 |
2 |2017-12-09 |2 |38.000 |37.0000000000000000 |
2 |2017-12-12 |2 |38.000 |37.2000000000000000 |
2 |2017-12-13 |2 |26.000 |35.3333333333333333 |
3 |2017-12-06 |1 |10.000 |10.0000000000000000 |
3 |2017-12-07 |1 |10.000 |10.0000000000000000 |
3 |2017-12-08 |1 |10.500 |10.1666666666666667 |
3 |2017-12-09 |1 |11.000 |10.3750000000000000 |
3 |2017-12-12 |1 |40.000 |16.3000000000000000 |
3 |2017-12-13 |1 |16.000 |16.2500000000000000 |
3 |2017-12-06 |2 |22.000 |22.0000000000000000 |
3 |2017-12-07 |2 |23.000 |22.5000000000000000 |
3 |2017-12-08 |2 |21.000 |22.0000000000000000 |
3 |2017-12-09 |2 |28.000 |23.5000000000000000 |
3 |2017-12-12 |2 |20.000 |22.8000000000000000 |
3 |2017-12-13 |2 |22.000 |22.6666666666666667 |
4 |2017-12-06 |1 |714.000 |714.0000000000000000 |
4 |2017-12-07 |1 |633.000 |673.5000000000000000 |
4 |2017-12-08 |1 |384.000 |577.0000000000000000 |
4 |2017-12-09 |1 |243.000 |493.5000000000000000 |
4 |2017-12-12 |1 |1.000 |395.0000000000000000 |
4 |2017-12-13 |1 |6.000 |330.1666666666666667 |
4 |2017-12-06 |2 |349.000 |349.0000000000000000 |
4 |2017-12-07 |2 |252.000 |300.5000000000000000 |
4 |2017-12-08 |2 |38.000 |213.0000000000000000 |
4 |2017-12-09 |2 |71.000 |177.5000000000000000 |
4 |2017-12-12 |2 |104.000 |162.8000000000000000 |ok je m'incline !
c'est pas évident, faut le savoir !
merci de cette réponse rapide