Présentation du<br/><br/>Système d'Information creation_bdd:fonctions

Présentation du<br/><br/>Système d'Information creation_bdd:fonctions http://sig.cenlr.org/ Sun, 26 Apr 2026 11:03:35 +0200 FeedCreator 1.7.2-ppt DokuWiki http://sig.cenlr.org/lib/images/favicon.ico Présentation du<br/><br/>Système d'Information http://sig.cenlr.org/ creation_bdd:fonctions:cendiffnodes http://sig.cenlr.org/creation_bdd/fonctions/cendiffnodes <div class="tags"><span> <a href="http://sig.cenlr.org/tag/postgis?do=showtag&tag=PostGIS" class="wikilink1" title="tag:postgis" rel="tag">PostGIS</a>, <a href="http://sig.cenlr.org/tag/plpgsql?do=showtag&tag=plpgsql" class="wikilink1" title="tag:plpgsql" rel="tag">plpgsql</a>, <a href="http://sig.cenlr.org/tag/dblink?do=showtag&tag=dblink" class="wikilink1" title="tag:dblink" rel="tag">dblink</a>, <a href="http://sig.cenlr.org/tag/topologie?do=showtag&tag=topologie" class="wikilink1" title="tag:topologie" rel="tag">topologie</a> </span></div> <h1 class="sectionedit1"><a name="affichage_des_nœuds_non_communs_entre_2_geometries" id="affichage_des_nœuds_non_communs_entre_2_geometries">Affichage des nœuds non communs entre 2 géométries</a></h1> <div class="level1"> </div>  <h3 class="sectionedit2"><a name="problematique" id="problematique">Problèmatique</a></h3> <div class="level3"> <p> Une table de la base de données contient des périmètres ZNIEFF, de type 1 et de type type 2. Les périmètres de type 1 peuvent être inclus dans une znieff de type 2. Si cette inclusion existe, elle est forcément totale. </p> <p> Le problème rencontré est la nécessité de corriger les géométries des znieff de type 1 qui devraient être jointives sur une partie de leur contour avec une type 2. </p> <p> La requête ci dessous permet d'afficher les nœuds de la géométrie de la znieff de type 1 non communs avec les nœuds de la géométrie de la ZNIEFF de type 2. </p> <p> La version de PostGIS de la base de données utilisée en production ne propose pas certaines fonctions que nous souhaitons utiliser. C'est pourquoi nous utilisons dblink, afin d'utiliser les données de la base de productiona avec la base en développement (PostGIS 1.5). </p> <p> Intégrer cette fonctionnalité à une fonction : </p> <pre class="code sql"><span class="kw1">TRUNCATE</span> point_a_corriger; <span class="kw1">INSERT</span> <span class="kw1">INTO</span> point_a_corriger   <span class="coMULTI">/* affiche tous les points non communs entre deux géométrie et distant de moins de x mètre de la seconde */</span>   <span class="kw1">SELECT</span> row_number<span class="br0">(</span><span class="br0">)</span> over<span class="br0">(</span><span class="br0">)</span><span class="sy0">,</span> geom <span class="kw1">AS</span> geometrie <span class="kw1">FROM</span> <span class="br0">(</span> <span class="kw1">SELECT</span> <span class="br0">(</span>ST_DumpPoints<span class="br0">(</span>geometrie<span class="br0">)</span><span class="br0">)</span><span class="sy0">.*</span> <span class="kw1">FROM</span> dblink<span class="br0">(</span><span class="st0">'dbname=znieff port=5432 host=192.168.1.40 user=dba password=dbroot'</span><span class="sy0">,</span> <span class="st0">'select id_znieff, geometrie from znieff_validees where id_znieff=1081'</span><span class="br0">)</span> <span class="kw1">AS</span> znieff<span class="br0">(</span>id integer<span class="sy0">,</span> geometrie geometry<span class="br0">)</span><span class="br0">)</span> sub <span class="kw1">WHERE</span> ST_ASText<span class="br0">(</span>geom<span class="br0">)</span> <span class="kw1">NOT</span> <span class="kw1">IN</span> <span class="br0">(</span> <span class="kw1">SELECT</span> ST_ASText<span class="br0">(</span>geom<span class="br0">)</span> <span class="kw1">AS</span> a <span class="kw1">FROM</span> <span class="br0">(</span> <span class="kw1">SELECT</span> <span class="br0">(</span>ST_DumpPoints<span class="br0">(</span>geometrie<span class="br0">)</span><span class="br0">)</span><span class="sy0">.*</span> <span class="kw1">FROM</span> dblink<span class="br0">(</span><span class="st0">'dbname=znieff port=5432 host=192.168.1.40 user=dba password=dbroot'</span><span class="sy0">,</span> <span class="st0">'select id_znieff, geometrie from znieff_validees where id_znieff=930'</span><span class="br0">)</span> <span class="kw1">AS</span> znieff<span class="br0">(</span>id integer<span class="sy0">,</span> geometrie geometry<span class="br0">)</span><span class="br0">)</span> sub2<span class="br0">)</span></pre> </div>  PostGIS plpgsql dblink topologie Sun, 07 Aug 2011 20:14:56 +0200 creation_bdd:fonctions:ceninsertionligneflore http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionligneflore creation_bdd:fonctions Sat, 06 Feb 2010 13:21:45 +0200 creation_bdd:fonctions:ceninsertionperimetrefaune http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionperimetrefaune creation_bdd:fonctions Sat, 06 Feb 2010 13:21:45 +0200 creation_bdd:fonctions:ceninsertionperimetreflore http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionperimetreflore creation_bdd:fonctions Sat, 06 Feb 2010 13:21:46 +0200 creation_bdd:fonctions:ceninsertionperimetrehabitat http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionperimetrehabitat creation_bdd:fonctions Sat, 06 Feb 2010 13:21:46 +0200 creation_bdd:fonctions:ceninsertionpointfaune http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionpointfaune creation_bdd:fonctions Sat, 06 Feb 2010 13:21:46 +0200 creation_bdd:fonctions:ceninsertionpointflore http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionpointflore creation_bdd:fonctions Sat, 06 Feb 2010 13:21:46 +0200 creation_bdd:fonctions:ceninsertionpointhabitat http://sig.cenlr.org/creation_bdd/fonctions/ceninsertionpointhabitat creation_bdd:fonctions Sat, 06 Feb 2010 13:21:46 +0200 creation_bdd:fonctions:cenlistestructureauteurese http://sig.cenlr.org/creation_bdd/fonctions/cenlistestructureauteurese creation_bdd:fonctions Sat, 06 Feb 2010 13:21:46 +0200 creation_bdd:fonctions:cenlistestructurefinanceetude http://sig.cenlr.org/creation_bdd/fonctions/cenlistestructurefinanceetude creation_bdd:fonctions Sat, 06 Feb 2010 13:21:47 +0200 creation_bdd:fonctions:code_taxon_europeen http://sig.cenlr.org/creation_bdd/fonctions/code_taxon_europeen <div class="tags"><span> <a href="http://sig.cenlr.org/tag/postgresql?do=showtag&tag=PostgreSQL" class="wikilink1" title="tag:postgresql" rel="tag">PostgreSQL</a>, <a href="http://sig.cenlr.org/tag/plpython?do=showtag&tag=plpython" class="wikilink1" title="tag:plpython" rel="tag">plpython</a>, <a href="http://sig.cenlr.org/tag/inspire?do=showtag&tag=INSPIRE" class="wikilink1" title="tag:inspire" rel="tag">INSPIRE</a>, <a href="http://sig.cenlr.org/tag/soap?do=showtag&tag=SOAP" class="wikilink1" title="tag:soap" rel="tag">SOAP</a>, <a href="http://sig.cenlr.org/tag/species?do=showtag&tag=species" class="wikilink1" title="tag:species" rel="tag">species</a> </span></div> <h1 class="sectionedit3"><a name="connaitre_le_code_europeen_d_un_taxon" id="connaitre_le_code_europeen_d_un_taxon">Connaitre le code européen d'un taxon</a></h1> <div class="level1"> <div class="box round red" style="width: 100%; "> <b class='xtop'><b class='xb1'></b><b class='xb2'></b><b class='xb3'></b><b class='xb4'></b></b> <div class='xbox'> <div class='box_content'> <strong>Merci à tous de partager les améliorations que vous apporteriez sur cette fonction</strong> </div> </div> <b class='xbottom'><b class='xb4'></b><b class='xb3'></b><b class='xb2'></b><b class='xb1'></b></b> </div> <ul> <li class="level1"><div class="li"> Service <acronym title="Simple Object Access Protocol">SOAP</acronym> utilisé : <a href="http://www.eu-nomen.eu/portal/soap.php" class="urlextern" target="_blank" title="http://www.eu-nomen.eu/portal/soap.php" rel="nofollow">http://www.eu-nomen.eu/portal/soap.php</a></div> </li> <li class="level1"><div class="li"> Conditions d'utilisation : non mentionnées par le fournisseur du service</div> </li> </ul> </div>  <h3 class="sectionedit4"><a name="creation_de_la_fonction_dans_le_schema_outils" id="creation_de_la_fonction_dans_le_schema_outils">Création de la fonction dans le schéma "outils"</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> outils<span class="sy0">.</span>eu_nomen_taxa_code<span class="br0">(</span><span class="st0">"scientific_name"</span> text<span class="br0">)</span> RETURNS text <span class="kw1">AS</span> $BODY$ import urllib2; import httplib; <span class="kw1">FROM</span> urllib import urlencode <span class="kw1">FROM</span> urllib2 import Request<span class="sy0">,</span> urlopen   # définition de l<span class="st0">'URL du service url = '</span>http:<span class="sy0">//</span>www<span class="sy0">.</span>eu<span class="sy0">-</span>nomen<span class="sy0">.</span>eu<span class="sy0">/</span>portal<span class="sy0">/</span>soap<span class="sy0">.</span>php<span class="st0">'   # les données SOAP à poster au service postdata='</span><span class="sy0"><</span>?xml version<span class="sy0">=</span><span class="st0">"1.0"</span> encoding<span class="sy0">=</span><span class="st0">"UTF-8"</span> standalone<span class="sy0">=</span><span class="st0">"yes"</span>?<span class="sy0">><</span>soap:Envelope xmlns:soap<span class="sy0">=</span><span class="st0">"http://schemas.xmlsoap.org/soap/envelope/"</span> soap:encodingStyle<span class="sy0">=</span><span class="st0">"http://www.w3.org/2001/12/soap-encoding"</span><span class="sy0">><</span>soap:Header<span class="sy0">/><</span>soap:Body<span class="sy0">><</span>getGUID xmlns<span class="sy0">=</span><span class="st0">"http://PESI/v0.1"</span><span class="sy0">><</span>scientificname<span class="sy0">></span><span class="st0">'+scientific_name+'</span><span class="sy0"></</span>scientificname<span class="sy0">></</span>getGUID<span class="sy0">><</span>soap:Fault<span class="sy0">/></</span>soap:Body<span class="sy0">></</span>soap:Envelope<span class="sy0">></span><span class="st0">'   # l'</span>entête headers <span class="sy0">=</span> <span class="br0">{</span><span class="st0">'Content-Type'</span>: <span class="st0">'application/soap+xml; charset=utf-8'</span><span class="sy0">,</span><span class="st0">'Content-Type'</span>: <span class="st0">'text/xml; charset=utf-8'</span><span class="sy0">,</span><span class="st0">'Content-Length'</span>: str<span class="br0">(</span>len<span class="br0">(</span>postdata<span class="br0">)</span><span class="br0">)</span><span class="br0">}</span> req <span class="sy0">=</span> Request<span class="br0">(</span>url<span class="sy0">,</span> postdata<span class="sy0">,</span> headers<span class="br0">)</span> reponse <span class="sy0">=</span> urlopen<span class="br0">(</span>req<span class="br0">)</span> text <span class="sy0">=</span> reponse<span class="sy0">.</span><span class="kw1">READ</span><span class="br0">(</span><span class="br0">)</span>   rv <span class="sy0">=</span> plpy<span class="sy0">.</span>execute<span class="br0">(</span><span class="st0">"SELECT ((xpath('//SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:getGUIDResponse/return/text()', '"</span><span class="sy0">+</span>text<span class="sy0">+</span><span class="st0">"'::xml, ARRAY[ARRAY['SOAP-ENV','http://schemas.xmlsoap.org/soap/envelope/'],ARRAY['SOAP-ENC','http://schemas.xmlsoap.org/soap/encoding/'],ARRAY['xsd','http://www.w3.org/2001/XMLSchema'],ARRAY['xsi','http://www.w3.org/2001/XMLSchema-instance'],ARRAY['ns1','http://PESI/v0.1']]))[1])::text AS code_taxon"</span><span class="br0">)</span> eu_nomen_code <span class="sy0">=</span> rv<span class="br0">[</span><span class="nu0">0</span><span class="br0">]</span><span class="br0">[</span><span class="st0">'code_taxon'</span><span class="br0">]</span> <span class="kw1">RETURN</span> eu_nomen_code   <span class="kw1">RETURN</span> text $BODY$ <span class="kw1">LANGUAGE</span> plpythonu VOLATILE COST <span class="nu0">100</span>; <span class="kw1">ALTER</span> <span class="kw1">FUNCTION</span> outils<span class="sy0">.</span>eu_nomen_taxa_code<span class="br0">(</span><span class="st0">"scientific_name"</span> text<span class="br0">)</span> OWNER <span class="kw1">TO</span> dba; <span class="kw1">SELECT</span> outils<span class="sy0">.</span>eu_nomen_taxa_code<span class="br0">(</span><span class="st0">'passer domesticus'</span><span class="br0">)</span>; COMMENT <span class="kw1">ON</span> <span class="kw1">FUNCTION</span> outils<span class="sy0">.</span>eu_nomen_taxa_code<span class="br0">(</span><span class="st0">"scientific_name"</span> text<span class="br0">)</span> <span class="kw1">IS</span> <span class="st0">'Retourne le code européen d'</span><span class="st0">'un taxon dont le nom d'</span><span class="st0">'espèce (sans auteur est passé en paramètre). Un grand merci à Dorian pour son micro cours sur SOAP!'</span></pre> </div>  <h3 class="sectionedit5"><a name="utilisation" id="utilisation">Utilisation</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">SELECT</span> outils<span class="sy0">.</span>eu_nomen_taxa_code<span class="br0">(</span><span class="st0">'passer domestcus'</span><span class="br0">)</span></pre> <p> Retourne : <strong><a href="http://www.eu-nomen.eu/portal/taxon.php?GUID=urn:lsid:faunaeur.org:taxname:97437" class="urlextern" target="_blank" title="http://www.eu-nomen.eu/portal/taxon.php?GUID=urn:lsid:faunaeur.org:taxname:97437" rel="nofollow">urn:lsid:faunaeur.org:taxname:97437</a></strong> </p> </div>  PostgreSQL plpython INSPIRE SOAP species Sun, 07 Aug 2011 13:42:24 +0200 creation_bdd:fonctions:elevation http://sig.cenlr.org/creation_bdd/fonctions/elevation <div class="tags"><span> <a href="http://sig.cenlr.org/tag/postgis?do=showtag&tag=PostGIS" class="wikilink1" title="tag:postgis" rel="tag">PostGIS</a>, <a href="http://sig.cenlr.org/tag/plpython?do=showtag&tag=plpython" class="wikilink1" title="tag:plpython" rel="tag">plpython</a>, <a href="http://sig.cenlr.org/tag/webservcies?do=showtag&tag=WebServcies" class="wikilink1" title="tag:webservcies" rel="tag">WebServcies</a>, <a href="http://sig.cenlr.org/tag/elevation?do=showtag&tag=elevation" class="wikilink1" title="tag:elevation" rel="tag">elevation</a>, <a href="http://sig.cenlr.org/tag/sh?do=showtag&tag=sh" class="wikilink1" title="tag:sh" rel="tag">sh</a> </span></div> <h1 class="sectionedit6"><a name="connaitre_l_altitude_d_un_point_grace_a_ses_coordonnees" id="connaitre_l_altitude_d_un_point_grace_a_ses_coordonnees">Connaitre l'altitude d'un point grâce à ses coordonnées</a></h1> <div class="level1"> <div class="box round red" style="width: 100%; "> <b class='xtop'><b class='xb1'></b><b class='xb2'></b><b class='xb3'></b><b class='xb4'></b></b> <div class='xbox'> <div class='box_content'> <strong>Merci à tous de partager les améliorations que vous apporteriez sur cette fonction</strong> </div> </div> <b class='xbottom'><b class='xb4'></b><b class='xb3'></b><b class='xb2'></b><b class='xb1'></b></b> </div> <ul> <li class="level1"><div class="li"> Service utilisé : <a href="http://www.earthtools.org/webservices.htm#height" class="urlextern" target="_blank" title="http://www.earthtools.org/webservices.htm#height" rel="nofollow">http://www.earthtools.org/webservices.htm#height</a></div> </li> <li class="level1"><div class="li"> Données utilisées : <a href="http://www.earthtools.org/aboutdata.htm" class="urlextern" target="_blank" title="http://www.earthtools.org/aboutdata.htm" rel="nofollow">http://www.earthtools.org/aboutdata.htm</a></div> </li> <li class="level1"><div class="li"> Conditions d'utilisation : <a href="http://www.earthtools.org/webservices.htm#usage" class="urlextern" target="_blank" title="http://www.earthtools.org/webservices.htm#usage" rel="nofollow">http://www.earthtools.org/webservices.htm#usage</a></div> </li> </ul> </div>  <h3 class="sectionedit7"><a name="creation_de_la_fonction_dans_le_schema_outils" id="creation_de_la_fonction_dans_le_schema_outils">Création de la fonction dans le schéma "outils"</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> outils<span class="sy0">.</span>elevation<span class="br0">(</span><span class="st0">"long"</span> double precision<span class="sy0">,</span> lat double precision<span class="br0">)</span> RETURNS double precision <span class="kw1">AS</span> $BODY$ import urllib; page<span class="sy0">=</span>urllib<span class="sy0">.</span>urlopen<span class="br0">(</span><span class="st0">'http://www.earthtools.org/height/'</span><span class="sy0">+</span>str<span class="br0">(</span>lat<span class="br0">)</span><span class="sy0">+</span><span class="st0">'/'</span><span class="sy0">+</span>str<span class="br0">(</span>long<span class="br0">)</span><span class="br0">)</span>; result_xml <span class="sy0">=</span> page<span class="sy0">.</span><span class="kw1">READ</span><span class="br0">(</span><span class="br0">)</span> rv <span class="sy0">=</span> plpy<span class="sy0">.</span>execute<span class="br0">(</span><span class="st0">"SELECT ((xpath('//height/meters/text()', '"</span><span class="sy0">+</span>result_xml<span class="sy0">+</span><span class="st0">"'::xml))[1])::text::float AS elevation"</span><span class="br0">)</span> foo <span class="sy0">=</span> rv<span class="br0">[</span><span class="nu0">0</span><span class="br0">]</span><span class="br0">[</span><span class="st0">'elevation'</span><span class="br0">]</span> <span class="kw1">RETURN</span> foo $BODY$ <span class="kw1">LANGUAGE</span> plpythonu;</pre> </div>  <h3 class="sectionedit8"><a name="creation_d_un_script_de_mise_a_jour_d_une_table_d_observation" id="creation_d_un_script_de_mise_a_jour_d_une_table_d_observation">Création d'un script de mise à jour d'une table d'observation</a></h3> <div class="level3"> <pre class="code bash"><span class="co0">#!/bin/sh</span> <span class="kw1">for</span> a <span class="kw1">in</span> <span class="sy0">`</span><span class="kw2">seq</span> <span class="nu0">1</span> <span class="nu0">20000</span><span class="sy0">`</span>; <span class="kw1">do</span> psql <span class="re5">-c</span> <span class="st0">"UPDATE saisie.saisie_observation SET elevation = outils.elevation(st_x(st_transform(geometrie, 4326)), st_y(st_transform(geometrie, 4326))) WHERE id_entite IN (SELECT id_entite FROM saisie.saisie_observation WHERE elevation IS NULL AND GeometryType(geometrie) = 'POINT' ORDER BY id_entite LIMIT 1)"</span> <span class="re5">-h</span> 192.168.1.230 <span class="re5">-U</span> nom_utilisateur nom_base_de_donnee   <span class="co0"># respect des conditions d'utilisation du service </span> <span class="co0"># interdisant plus d'une requête par seconde</span> <span class="kw2">sleep</span> <span class="nu0">1</span>; <span class="co0"># echo "Élévation mise à jour"</span> <span class="kw1">done</span></pre> </div>  PostGIS plpython WebServcies elevation sh Sun, 07 Aug 2011 20:11:23 +0200 creation_bdd:fonctions:fonctioncenattributionauteurpointfaune http://sig.cenlr.org/creation_bdd/fonctions/fonctioncenattributionauteurpointfaune creation_bdd:fonctions Sat, 06 Feb 2010 13:21:47 +0200 creation_bdd:fonctions:fonctioncenattributionauteurpointflore http://sig.cenlr.org/creation_bdd/fonctions/fonctioncenattributionauteurpointflore creation_bdd:fonctions Sat, 06 Feb 2010 13:21:47 +0200 creation_bdd:fonctions:fonctioncenattributionhabitatpointhabitat http://sig.cenlr.org/creation_bdd/fonctions/fonctioncenattributionhabitatpointhabitat creation_bdd:fonctions Sat, 06 Feb 2010 13:21:47 +0200 creation_bdd:fonctions:fonctioncenattributionstructureauteurpointfaune http://sig.cenlr.org/creation_bdd/fonctions/fonctioncenattributionstructureauteurpointfaune creation_bdd:fonctions Sat, 06 Feb 2010 13:21:47 +0200 creation_bdd:fonctions:fonctioncenlisteobservateursespece http://sig.cenlr.org/creation_bdd/fonctions/fonctioncenlisteobservateursespece <h2 class="sectionedit9"><a name="liste_formatee_des_observateurs_d_une_espece" id="liste_formatee_des_observateurs_d_une_espece">Liste formatée des observateurs d'une espèce</a></h2> <div class="level2"> <p> Cette fonction retourne pour l'espèce passée en paramètre (cd_noom) la liste des Observateurs classés par ordre alphabétique.. </p> </div>  <h3 class="sectionedit10"><a name="nom_de_la_fonction" id="nom_de_la_fonction">Nom de la fonction</a></h3> <div class="level3"> <p> <strong>md.liste_noms_observateurs_espece(text)</strong> </p> </div>  <h3 class="sectionedit11"><a name="parametres" id="parametres">Paramètres</a></h3> <div class="level3"> <p> le code du taxon (attribut cd_nom) </p> </div>  <h3 class="sectionedit12"><a name="resultat_retourne" id="resultat_retourne">Résultat retourné</a></h3> <div class="level3"> <p> La liste des Observateurs classés par ordre alphabétique </p> </div>  <h3 class="sectionedit13"><a name="script_plpgsql" id="script_plpgsql">Script pl/pgsql</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> md<span class="sy0">.</span>liste_noms_observateurs_espece<span class="br0">(</span>text<span class="br0">)</span> RETURNS text <span class="kw1">AS</span> $BODY$ DECLARE myrec RECORD; var_cd_nom ALIAS <span class="kw1">FOR</span> $<span class="nu0">1</span>; var_liste_nom_observateur character varying <span class="kw1">DEFAULT</span> <span class="st0">''</span>; BEGIN <span class="kw1">FOR</span> myrec <span class="kw1">IN</span> <span class="kw1">SELECT</span> TRIM<span class="br0">(</span>COALESCE<span class="br0">(</span>nom<span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="sy0">||</span><span class="st0">' '</span><span class="sy0">||</span>COALESCE<span class="br0">(</span>prenom<span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="br0">)</span> <span class="kw1">AS</span> nom_prenom <span class="kw1">FROM</span> md<span class="sy0">.</span>personne <span class="kw1">WHERE</span> code_personne <span class="kw1">IN</span> <span class="br0">(</span><span class="kw1">SELECT</span> <span class="kw1">DISTINCT</span> regexp_split_to_table<span class="br0">(</span>observateur<span class="sy0">,</span> E<span class="st0">'<span class="es0">\&</span>'</span><span class="br0">)</span> <span class="kw1">FROM</span> saisie<span class="sy0">.</span>saisie_observation <span class="kw1">WHERE</span> cd_nom <span class="sy0">=</span> var_cd_nom<span class="br0">)</span> <span class="kw1">ORDER</span> <span class="kw1">BY</span> TRIM<span class="br0">(</span>COALESCE<span class="br0">(</span>nom<span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="sy0">||</span><span class="st0">' '</span><span class="sy0">||</span>COALESCE<span class="br0">(</span>prenom<span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="br0">)</span> <span class="kw1">ASC</span> LOOP var_liste_nom_observateur:<span class="sy0">=</span>var_liste_nom_observateur<span class="sy0">||</span><span class="st0">', '</span><span class="sy0">||</span>myrec<span class="sy0">.</span>nom_prenom; END LOOP; <span class="kw1">RETURN</span> substring<span class="br0">(</span>var_liste_nom_observateur <span class="kw1">FROM</span> <span class="nu0">3</span> <span class="kw1">FOR</span> <span class="br0">(</span>length<span class="br0">(</span>var_liste_nom_observateur<span class="br0">)</span><span class="br0">)</span>::integer<span class="br0">)</span>; END; $BODY$ <span class="kw1">LANGUAGE</span> plpgsql VOLATILE COST <span class="nu0">100</span>; <span class="kw1">ALTER</span> <span class="kw1">FUNCTION</span> md<span class="sy0">.</span>liste_noms_observateurs_espece<span class="br0">(</span>text<span class="br0">)</span> OWNER <span class="kw1">TO</span> dba;   <span class="kw1">SELECT</span> md<span class="sy0">.</span>liste_noms_observateurs_espece<span class="br0">(</span><span class="st0">'65254'</span><span class="br0">)</span>;</pre> </div>  creation_bdd:fonctions Wed, 20 Jul 2011 17:16:06 +0200 creation_bdd:fonctions:fonctioncenlistepersonneauteurese http://sig.cenlr.org/creation_bdd/fonctions/fonctioncenlistepersonneauteurese <h1 class="sectionedit14"><a name="liste_formatee_des_auteurs_d_une_donnee" id="liste_formatee_des_auteurs_d_une_donnee">Liste formatée des auteurs d'une donnée</a></h1> <div class="level1"> <p> Cette fonction retourne pour l'entité passée en paramètre la liste des codes des auteurs séparés par le caractère “$”. </p> <p> Les auteurs d'une ese sont stockés dans la table <strong>“personne_est_auteur_donnee”</strong> par 3 attributs clefs primaires : - l'identifiant de la donnée :<strong>“id_entite”</strong> - le code de l'observateur : <strong>“code_personne”</strong> - l'ordre de cet observateur : <strong>“ordre”</strong> </p> <p> Or, traditionnellement, les auteurs sont présentés à la suite les un des autres, dans une colonne auteur. Notamment dans les fichiers SIG traditionnels et les tableurs. Le stockage “en colonne” de ces auteurs rend pourtont l'exploitation de cette métadonnée importante difficile. Cette fonction reproduit pour chaque donnée la liste de ses auteurs qu'elle sépare par le caractère <strong>“$”</strong>. </p> </div>  <h3 class="sectionedit15"><a name="nom_de_la_fonction" id="nom_de_la_fonction">- Nom de la fonction</a></h3> <div class="level3"> <p> <strong>cen_liste_personne_auteur_ese(integer)</strong> </p> </div>  <h3 class="sectionedit16"><a name="parametres" id="parametres">- Paramètres</a></h3> <div class="level3"> <p> l'identifiant de l'ese (<strong>entier</strong>) </p> </div>  <h3 class="sectionedit17"><a name="resulat_retourne" id="resulat_retourne">- Résulat retourné</a></h3> <div class="level3"> <p> La liste des codes d'auteurs (séparés par des “$”) de l'ese considérée (<strong>text</strong>) </p> </div>  <h3 class="sectionedit18"><a name="script_plpgsql" id="script_plpgsql">- Script pl/pgsql</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">FUNCTION</span> cen_liste_personne_auteur_ese<span class="br0">(</span>integer<span class="br0">)</span> RETURNS text <span class="kw1">AS</span> $_$ DECLARE id_entite alias <span class="kw1">FOR</span> $<span class="nu0">1</span>; myrec RECORD; var_code_personne character varying<span class="br0">(</span><span class="nu0">5</span><span class="br0">)</span>; var_liste_auteur character varying<span class="br0">(</span><span class="nu0">50</span><span class="br0">)</span> <span class="kw1">DEFAULT</span> <span class="st0">''</span>; BEGIN <span class="kw1">FOR</span> myrec <span class="kw1">IN</span> <span class="kw1">SELECT</span> id_entite<span class="sy0">,</span> code_personne<span class="sy0">,</span> ordre <span class="kw1">FROM</span> personne_est_auteur_donnee <span class="kw1">WHERE</span> personne_est_auteur_donnee<span class="sy0">.</span>id_entite<span class="sy0">=</span>id_entite <span class="kw1">ORDER</span> <span class="kw1">BY</span> ordre <span class="kw1">ASC</span> LOOP var_code_personne:<span class="sy0">=</span>myrec<span class="sy0">.</span>code_personne; var_liste_auteur:<span class="sy0">=</span>var_liste_auteur<span class="sy0">||</span><span class="st0">'$'</span><span class="sy0">||</span>var_code_personne; END LOOP; <span class="kw1">RETURN</span> ltrim<span class="br0">(</span>var_liste_auteur<span class="sy0">,</span><span class="st0">'$'</span><span class="br0">)</span>;   END; $_$ <span class="kw1">LANGUAGE</span> plpgsql;</pre> </div>  creation_bdd:fonctions Thu, 11 Aug 2011 11:26:37 +0200 creation_bdd:fonctions:no_html_special_char http://sig.cenlr.org/creation_bdd/fonctions/no_html_special_char <h1 class="sectionedit19"><a name="fonction_de_remplacement_des_code_html_des_caracteres_speciaux_par_les_caracteres" id="fonction_de_remplacement_des_code_html_des_caracteres_speciaux_par_les_caracteres">Fonction de remplacement des code HTML des caractères spéciaux par les caractères</a></h1> <div class="level1"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> no_html_specials_chars<span class="br0">(</span>text<span class="br0">)</span> RETURNS text <span class="kw1">AS</span> $BODY$ DECLARE var_texte ALIAS <span class="kw1">FOR</span> $<span class="nu0">1</span>; var_chaine text; BEGIN var_chaine:<span class="sy0">=</span>var_texte; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&quot;'</span><span class="sy0">,</span><span class="st0">'"'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&amp;'</span><span class="sy0">,</span><span class="st0">'&'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&euro;'</span><span class="sy0">,</span><span class="st0">'€'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&lt;'</span><span class="sy0">,</span><span class="st0">'<'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&gt;'</span><span class="sy0">,</span><span class="st0">'>'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&oelig;'</span><span class="sy0">,</span><span class="st0">'œ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">''</span><span class="sy0">,</span><span class="st0">'z'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Yuml;'</span><span class="sy0">,</span><span class="st0">'Y'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&nbsp;'</span><span class="sy0">,</span><span class="st0">'Space'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&iexcl;'</span><span class="sy0">,</span><span class="st0">'¡'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&cent;'</span><span class="sy0">,</span><span class="st0">'¢'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&pound;'</span><span class="sy0">,</span><span class="st0">'£'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&curren;'</span><span class="sy0">,</span><span class="st0">'¤'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&yen'</span><span class="sy0">,</span><span class="st0">'¥'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&brvbar;'</span><span class="sy0">,</span><span class="st0">'¦'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&sect;'</span><span class="sy0">,</span><span class="st0">'§'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&uml;'</span><span class="sy0">,</span><span class="st0">'¨'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&copy;'</span><span class="sy0">,</span><span class="st0">'©'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ordf;'</span><span class="sy0">,</span><span class="st0">'ª'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&laquo;'</span><span class="sy0">,</span><span class="st0">'«'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&not;'</span><span class="sy0">,</span><span class="st0">'¬'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&shy;'</span><span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&reg;'</span><span class="sy0">,</span><span class="st0">'®'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&masr;'</span><span class="sy0">,</span><span class="st0">'¯'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&deg;'</span><span class="sy0">,</span><span class="st0">'°'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&plusmn;'</span><span class="sy0">,</span><span class="st0">'±'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&sup2;'</span><span class="sy0">,</span><span class="st0">'²'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&sup3;'</span><span class="sy0">,</span><span class="st0">'³'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&acute;'</span><span class="sy0">,</span><span class="st0">''</span><span class="st0">''</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&micro;'</span><span class="sy0">,</span><span class="st0">'µ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&para;'</span><span class="sy0">,</span><span class="st0">'¶'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&middot;'</span><span class="sy0">,</span><span class="st0">'·'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&cedil;'</span><span class="sy0">,</span><span class="st0">'¸'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&sup1;'</span><span class="sy0">,</span><span class="st0">'¹'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ordm;'</span><span class="sy0">,</span><span class="st0">'º'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&raquo;'</span><span class="sy0">,</span><span class="st0">'»'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&frac14;'</span><span class="sy0">,</span><span class="st0">'¼'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&frac12;'</span><span class="sy0">,</span><span class="st0">'½'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&frac34;'</span><span class="sy0">,</span><span class="st0">'¾'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&iquest;'</span><span class="sy0">,</span><span class="st0">'¿'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Agrave;'</span><span class="sy0">,</span><span class="st0">'À'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Aacute;'</span><span class="sy0">,</span><span class="st0">'Á'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Acirc;'</span><span class="sy0">,</span><span class="st0">'Â'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Atilde;'</span><span class="sy0">,</span><span class="st0">'Ã'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Auml;'</span><span class="sy0">,</span><span class="st0">'Ä'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Aring;'</span><span class="sy0">,</span><span class="st0">'Å'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Aelig'</span><span class="sy0">,</span><span class="st0">'Æ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ccedil;'</span><span class="sy0">,</span><span class="st0">'Ç'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Egrave;'</span><span class="sy0">,</span><span class="st0">'È'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Eacute;'</span><span class="sy0">,</span><span class="st0">'É'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ecirc;'</span><span class="sy0">,</span><span class="st0">'Ê'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Euml;'</span><span class="sy0">,</span><span class="st0">'Ë'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Igrave;'</span><span class="sy0">,</span><span class="st0">'Ì'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Iacute;'</span><span class="sy0">,</span><span class="st0">'Í'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Icirc;'</span><span class="sy0">,</span><span class="st0">'Î'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Iuml;'</span><span class="sy0">,</span><span class="st0">'Ï'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&eth;'</span><span class="sy0">,</span><span class="st0">'Ð'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ntilde;'</span><span class="sy0">,</span><span class="st0">'Ñ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ograve;'</span><span class="sy0">,</span><span class="st0">'Ò'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Oacute;'</span><span class="sy0">,</span><span class="st0">'Ó'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ocirc;'</span><span class="sy0">,</span><span class="st0">'Ô'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Otilde;'</span><span class="sy0">,</span><span class="st0">'Õ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ouml;'</span><span class="sy0">,</span><span class="st0">'Ö'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&times;'</span><span class="sy0">,</span><span class="st0">'×'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Oslash;'</span><span class="sy0">,</span><span class="st0">'Ø'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ugrave;'</span><span class="sy0">,</span><span class="st0">'Ù'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Uacute;'</span><span class="sy0">,</span><span class="st0">'Ú'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Ucirc;'</span><span class="sy0">,</span><span class="st0">'Û'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Uuml;'</span><span class="sy0">,</span><span class="st0">'Ü'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&Yacute;'</span><span class="sy0">,</span><span class="st0">'Ý'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&thorn;'</span><span class="sy0">,</span><span class="st0">'Þ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&szlig;'</span><span class="sy0">,</span><span class="st0">'ß'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&agrave;'</span><span class="sy0">,</span><span class="st0">'à'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&aacute;'</span><span class="sy0">,</span><span class="st0">'á'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&acirc;'</span><span class="sy0">,</span><span class="st0">'â'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&atilde;'</span><span class="sy0">,</span><span class="st0">'ã'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&auml;'</span><span class="sy0">,</span><span class="st0">'ä'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&aring;'</span><span class="sy0">,</span><span class="st0">'å'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&aelig;'</span><span class="sy0">,</span><span class="st0">'æ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ccedil;'</span><span class="sy0">,</span><span class="st0">'ç'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&egrave;'</span><span class="sy0">,</span><span class="st0">'è'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&eacute;'</span><span class="sy0">,</span><span class="st0">'é'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ecirc;'</span><span class="sy0">,</span><span class="st0">'ê'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&euml;'</span><span class="sy0">,</span><span class="st0">'ë'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&igrave;'</span><span class="sy0">,</span><span class="st0">'ì'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&iacute;'</span><span class="sy0">,</span><span class="st0">'í'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&icirc;'</span><span class="sy0">,</span><span class="st0">'î'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&iuml;'</span><span class="sy0">,</span><span class="st0">'ï'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&eth;'</span><span class="sy0">,</span><span class="st0">'ð'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ntilde;'</span><span class="sy0">,</span><span class="st0">'ñ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ograve;'</span><span class="sy0">,</span><span class="st0">'ò'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&oacute;'</span><span class="sy0">,</span><span class="st0">'ó'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ocirc;'</span><span class="sy0">,</span><span class="st0">'ô'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&otilde;'</span><span class="sy0">,</span><span class="st0">'õ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ouml;'</span><span class="sy0">,</span><span class="st0">'ö'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&divide;'</span><span class="sy0">,</span><span class="st0">'÷'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&oslash;'</span><span class="sy0">,</span><span class="st0">'ø'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ugrave;'</span><span class="sy0">,</span><span class="st0">'ù'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&uacute;'</span><span class="sy0">,</span><span class="st0">'ú'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&ucirc;'</span><span class="sy0">,</span><span class="st0">'û'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&uuml;'</span><span class="sy0">,</span><span class="st0">'ü'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&yacute;'</span><span class="sy0">,</span><span class="st0">'ý'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&thorn;'</span><span class="sy0">,</span><span class="st0">'þ'</span><span class="br0">)</span>; var_chaine:<span class="sy0">=</span><span class="kw1">REPLACE</span><span class="br0">(</span>var_chaine<span class="sy0">,</span><span class="st0">'&yuml;'</span><span class="sy0">,</span><span class="st0">'ÿ'</span><span class="br0">)</span>; <span class="kw1">RETURN</span> var_chaine; END; $BODY$ <span class="kw1">LANGUAGE</span> plpgsql VOLATILE COST <span class="nu0">100</span>; <span class="kw1">ALTER</span> <span class="kw1">FUNCTION</span> no_html_specials_chars<span class="br0">(</span>text<span class="br0">)</span> OWNER <span class="kw1">TO</span> dba;</pre> </div> creation_bdd:fonctions Thu, 02 Dec 2010 12:59:00 +0200 creation_bdd:fonctions:suiv_operation_sur_table http://sig.cenlr.org/creation_bdd/fonctions/suiv_operation_sur_table <h1 class="sectionedit20"><a name="tracer_et_suivre_les_modifications_apportees_aux_donnees_d_une_table" id="tracer_et_suivre_les_modifications_apportees_aux_donnees_d_une_table">Tracer et suivre les modifications apportées aux données d'une table</a></h1> <div class="level1"> <p> <strong>Tiré de <a href="http://www.postgresonline.com/journal/archives/87-PostgreSQL-8.3-PLPGSQL-Cheatsheet-Overview.html" class="urlextern" target="_blank" title="http://www.postgresonline.com/journal/archives/87-PostgreSQL-8.3-PLPGSQL-Cheatsheet-Overview.html" rel="nofollow">http://www.postgresonline.com/journal/archives/87-PostgreSQL-8.3-PLPGSQL-Cheatsheet-Overview.html</a></strong> </p> </div>  <h2 class="sectionedit21"><a name="objectifs" id="objectifs">Objectifs</a></h2> <div class="level2"> <p> Savoir qui fait quoi et quand sur quelle donnée d'une table, garder l'historique de ces modifications </p> </div>  <h2 class="sectionedit22"><a name="point_de_depart" id="point_de_depart">Point de départ</a></h2> <div class="level2"> <ul> <li class="level1"><div class="li"> une table “ma_table” avec 4 attributs id_data, attr_1, attr_2 et attr_3</div> </li> </ul> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TABLE</span> ma_table <span class="br0">(</span> id_data serial <span class="kw1">NOT</span> <span class="kw1">NULL</span> <span class="kw1">PRIMARY</span> <span class="kw1">KEY</span><span class="sy0">,</span> attr_1 character varying<span class="sy0">,</span> attr_2 character varying<span class="sy0">,</span> attr_3 character varying <span class="br0">)</span>;</pre> </div>  <h2 class="sectionedit23"><a name="mise_en_œuvre" id="mise_en_œuvre">Mise en œuvre</a></h2> <div class="level2"> </div>  <h3 class="sectionedit24"><a name="creation_d_une_table_pour_faire_ce_suivi" id="creation_d_une_table_pour_faire_ce_suivi">création d'une table pour faire ce suivi</a></h3> <div class="level3"> <ul> <li class="level1"><div class="li"> elle contiendra les même colonnes que la table à suivre</div> </li> <li class="level1"><div class="li"> plus 3 attributs qui stockeront:</div> <ul> <li class="level2"><div class="li"> le nom du rôle qui effectue l'opération</div> </li> <li class="level2"><div class="li"> le type d'opération : insert, update ou delete</div> </li> <li class="level2"><div class="li"> la date et l'heure de l'opération</div> </li> </ul> </li> </ul> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TABLE</span> suivi_ma_table <span class="br0">(</span> operation character<span class="br0">(</span><span class="nu0">6</span><span class="br0">)</span><span class="sy0">,</span> date_operation timestamp without time zone <span class="kw1">NOT</span> <span class="kw1">NULL</span><span class="sy0">,</span> utilisateur character varying <span class="kw1">NOT</span> <span class="kw1">NULL</span><span class="sy0">,</span> id_data integer<span class="sy0">,</span> attr_1 character varying<span class="sy0">,</span> attr_2 character varying<span class="sy0">,</span> attr_3 character varying <span class="br0">)</span>;</pre> </div>  <h3 class="sectionedit25"><a name="creation_de_la_fonction_trigger_qui_se_chargera_de_ce_suivi" id="creation_de_la_fonction_trigger_qui_se_chargera_de_ce_suivi">Création de la fonction trigger qui se chargera de ce suivi</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> alimente_suivi_ma_table<span class="br0">(</span><span class="br0">)</span> RETURNS <span class="kw1">TRIGGER</span> <span class="kw1">AS</span> $BODY$ BEGIN <span class="co1">--</span> <span class="co1">-- Crée une ligne dans la table suivi qui reflète l'opération effectuées sur la table "ma_table",</span> <span class="co1">-- utilise la variable spéciale TG_OP qui décrit l'opération courante</span> <span class="co1">--</span> <span class="kw1">IF</span> <span class="br0">(</span>TG_OP <span class="sy0">=</span> <span class="st0">'DELETE'</span><span class="br0">)</span> THEN <span class="co1">-- en cas de suppression on stocke la ligne supprimée</span> <span class="kw1">INSERT</span> <span class="kw1">INTO</span> suivi_ma_table <span class="kw1">SELECT</span> <span class="st0">'DELETE'</span><span class="sy0">,</span> now<span class="br0">(</span><span class="br0">)</span><span class="sy0">,</span> user<span class="sy0">,</span> OLD<span class="sy0">.*</span>; <span class="kw1">RETURN</span> OLD; ELSIF <span class="br0">(</span>TG_OP <span class="sy0">=</span> <span class="st0">'UPDATE'</span><span class="br0">)</span> THEN <span class="co1">-- en cas de mise à jour on stocke la ligne dans son état "après mise à jour" </span> <span class="kw1">INSERT</span> <span class="kw1">INTO</span> suivi_ma_table <span class="st0">'UPDATE'</span><span class="sy0">,</span> now<span class="br0">(</span><span class="br0">)</span><span class="sy0">,</span> user<span class="sy0">,</span> NEW<span class="sy0">.*</span>; <span class="kw1">RETURN</span> NEW; ELSIF <span class="br0">(</span>TG_OP <span class="sy0">=</span> <span class="st0">'INSERT'</span><span class="br0">)</span> THEN <span class="co1">-- en cas d'insertion on stocke la ligne insérée</span> <span class="kw1">INSERT</span> <span class="kw1">INTO</span> suivi_ma_table <span class="kw1">SELECT</span> <span class="st0">'INSERT'</span><span class="sy0">,</span> now<span class="br0">(</span><span class="br0">)</span><span class="sy0">,</span> user<span class="sy0">,</span> NEW<span class="sy0">.*</span>; <span class="kw1">RETURN</span> NEW; END <span class="kw1">IF</span>; <span class="kw1">RETURN</span> <span class="kw1">NULL</span>; END; $BODY$ <span class="kw1">LANGUAGE</span> plpgsql VOLATILE COST <span class="nu0">100</span>;</pre> </div>  <h3 class="sectionedit26"><a name="creation_du_trigger" id="creation_du_trigger">Création du trigger</a></h3> <div class="level3"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">TRIGGER</span> suivi_saisie_observation AFTER <span class="kw1">INSERT</span> <span class="kw1">OR</span> <span class="kw1">UPDATE</span> <span class="kw1">OR</span> <span class="kw1">DELETE</span> <span class="kw1">ON</span> ma_table <span class="kw1">FOR</span> EACH ROW EXECUTE PROCEDURE alimente_suivi_ma_table<span class="br0">(</span><span class="br0">)</span>;</pre> </div>  <h2 class="sectionedit27"><a name="creation_d_un_flux_rss_pour_ce_suivi" id="creation_d_un_flux_rss_pour_ce_suivi">Création d'un flux rss pour ce suivi</a></h2> <div class="level2"> <p> le rôle de connexion “rss” aura les droit de select sur la table “ma_table” </p> <pre class="code php"><span class="kw2"><?php</span> <a target="_blank" href="http://www.php.net/header"><span class="kw3">header</span></a><span class="br0">(</span><span class="st0">"Content-Type: application/xml; charset=ISO-8859-1"</span><span class="br0">)</span><span class="sy0">;</span> <span class="re0">$dbcon</span><span class="sy0">=</span><a target="_blank" href="http://www.php.net/pg_connect"><span class="kw3">pg_connect</span></a><span class="br0">(</span><span class="st0">"host=192.168.1.210 dbname=test user=rss password=rss"</span><span class="br0">)</span><span class="sy0">;</span>   <span class="kw1">if</span> <span class="br0">(</span><a target="_blank" href="http://www.php.net/isset"><span class="kw3">isset</span></a><span class="br0">(</span><span class="re0">$_GET</span><span class="br0">[</span><span class="st_h">'operation'</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">&&</span><span class="re0">$_GET</span><span class="br0">[</span><span class="st_h">'operation'</span><span class="br0">]</span><span class="sy0">!=</span><span class="st_h">''</span><span class="br0">)</span> <span class="br0">{</span><span class="re0">$where</span> <span class="sy0">=</span> <span class="st_h">'lower(operation)=\''</span><span class="sy0">.</span> <span class="re0">$_GET</span><span class="br0">[</span><span class="st0">"operation"</span><span class="br0">]</span> <span class="sy0">.</span><span class="st_h">'\''</span><span class="sy0">;</span><span class="br0">}</span>   <span class="re0">$query</span> <span class="sy0">=</span> <span class="st_h">' SELECT operation, date_operation, utilisateur, id_data, attr_1, attr_2, attr_3 FROM suivi_ma_table ORDER BY date_operation DESC LIMIT 20; '</span><span class="sy0">;</span> <span class="co1">//echo $query;</span> <span class="re0">$item</span> <span class="sy0">=</span> <span class="st_h">'<?xml version="1.0" encoding="ISO-8859-1" ?></span> <rss version="2.0"> <channel> <title>Ma table - dernières modificationa appoertées</title> <link>http://www.google.fr</link> <description>Suivi de modifications apportées à la table ma_table</description> <language>Français</language>'; $result = pg_query($dbcon,$query); while($row = pg_fetch_array($result)) { $item.= ' <item> <title>'. utf8_decode($row["nom_complet"]) .'</title> <link>http://url_a_definir.org'</link> <description><![CDATA['.utf8_decode($row["operation"]) .' par '. utf8_decode($row["utilisateur"]) . ' - <em>donnée n°' . utf8_decode($row["id_data"]) . '</em>]]></description> </item>'; } $item.='</channel></rss>'; echo $item; ?></pre> </div>  creation_bdd:fonctions Tue, 22 Mar 2011 13:14:10 +0200 creation_bdd:fonctions:taxon_fils http://sig.cenlr.org/creation_bdd/fonctions/taxon_fils <div class="tags"><span> <a href="http://sig.cenlr.org/tag/postgesql?do=showtag&tag=PostgeSQL" class="wikilink1" title="tag:postgesql" rel="tag">PostgeSQL</a>, <a href="http://sig.cenlr.org/tag/plpgsql?do=showtag&tag=plpgsql" class="wikilink1" title="tag:plpgsql" rel="tag">plpgsql</a>, <a href="http://sig.cenlr.org/tag/inpn?do=showtag&tag=INPN" class="wikilink1" title="tag:inpn" rel="tag">INPN</a>, <a href="http://sig.cenlr.org/tag/taxref?do=showtag&tag=TAXREF" class="wikilink1" title="tag:taxref" rel="tag">TAXREF</a> </span></div> <h1 class="sectionedit28"><a name="connaitre_tous_les_taxons_situes_sous_un_nœud_de_l_arbre_taxonomique" id="connaitre_tous_les_taxons_situes_sous_un_nœud_de_l_arbre_taxonomique">Connaitre tous les taxons situés sous un nœud de l'arbre taxonomique</a></h1> <div class="level1"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> inpn<span class="sy0">.</span>taxons_fils<span class="br0">(</span>character varying<span class="br0">)</span> RETURNS SETOF character varying <span class="kw1">AS</span> $BODY$ DECLARE var_cd_nom alias <span class="kw1">FOR</span> $<span class="nu0">1</span>; BEGIN <span class="kw1">RETURN</span> QUERY <span class="kw1">WITH</span> RECURSIVE genealogie <span class="kw1">AS</span><span class="br0">(</span> <span class="kw1">SELECT</span> cd_nom<span class="sy0">,</span> cd_taxsup<span class="sy0">,</span> lb_nom <span class="kw1">FROM</span> inpn<span class="sy0">.</span>taxref <span class="kw1">WHERE</span> cd_nom<span class="sy0">=</span>lower<span class="br0">(</span>var_cd_nom<span class="br0">)</span> <span class="kw1">UNION</span> <span class="kw1">SELECT</span> inpn<span class="sy0">.</span>taxref<span class="sy0">.</span>cd_nom<span class="sy0">,</span> inpn<span class="sy0">.</span>taxref<span class="sy0">.</span>cd_taxsup<span class="sy0">,</span> inpn<span class="sy0">.</span>taxref<span class="sy0">.</span>lb_nom <span class="kw1">FROM</span> inpn<span class="sy0">.</span>taxref <span class="kw1">JOIN</span> genealogie <span class="kw1">ON</span> inpn<span class="sy0">.</span>taxref<span class="sy0">.</span>cd_taxsup <span class="sy0">=</span> genealogie<span class="sy0">.</span>cd_nom <span class="br0">)</span> <span class="kw1">SELECT</span> cd_nom <span class="kw1">FROM</span> genealogie; END; $BODY$ <span class="kw1">LANGUAGE</span> plpgsql VOLATILE SECURITY DEFINER COST <span class="nu0">100</span>; <span class="kw1">ALTER</span> <span class="kw1">FUNCTION</span> inpn<span class="sy0">.</span>taxons_fils<span class="br0">(</span>character varying<span class="br0">)</span> OWNER <span class="kw1">TO</span> dba;</pre> <p> Exemple : lister tous les odonates </p> <pre class="code sql"><span class="kw1">SELECT</span> inpn<span class="sy0">.</span>taxons_fils<span class="br0">(</span><span class="st0">'185356'</span><span class="br0">)</span>;</pre> </div>  PostgeSQL plpgsql INPN TAXREF Mon, 08 Aug 2011 09:10:15 +0200 creation_bdd:fonctions:utilitaires_de_date http://sig.cenlr.org/creation_bdd/fonctions/utilitaires_de_date <h1 class="sectionedit29"><a name="fonctions_utiles_pour_les_dates" id="fonctions_utiles_pour_les_dates">Fonctions utiles pour les dates</a></h1> <div class="level1"> </div>  <h2 class="sectionedit30"><a name="fonction_de_verification_de_l_existence_d_une_date" id="fonction_de_verification_de_l_existence_d_une_date">Fonction de vérification de l'existence d'une date</a></h2> <div class="level2"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> cenlr_date_est_valide<span class="br0">(</span>integer<span class="sy0">,</span> integer<span class="sy0">,</span> integer<span class="br0">)</span> RETURNS <span class="kw1">BOOLEAN</span> <span class="kw1">AS</span> $BODY$ DECLARE jour alias <span class="kw1">FOR</span> $<span class="nu0">1</span>; mois alias <span class="kw1">FOR</span> $<span class="nu0">2</span>; annee alias <span class="kw1">FOR</span> $<span class="nu0">3</span>; var_result <span class="kw1">BOOLEAN</span>; BEGIN <span class="kw1">IF</span> <span class="br0">(</span>mois <span class="kw1">IN</span> <span class="br0">(</span><span class="nu0">1</span><span class="sy0">,</span><span class="nu0">3</span><span class="sy0">,</span><span class="nu0">5</span><span class="sy0">,</span><span class="nu0">7</span><span class="sy0">,</span><span class="nu0">8</span><span class="sy0">,</span><span class="nu0">10</span><span class="sy0">,</span><span class="nu0">12</span><span class="br0">)</span> <span class="kw1">AND</span> jour <span class="sy0"><=</span><span class="nu0">31</span><span class="br0">)</span> THEN var_result <span class="sy0">=</span> true; <span class="kw1">RETURN</span> var_result; ELSIF <span class="br0">(</span>mois <span class="kw1">IN</span> <span class="br0">(</span><span class="nu0">4</span><span class="sy0">,</span><span class="nu0">6</span><span class="sy0">,</span><span class="nu0">9</span><span class="sy0">,</span><span class="nu0">11</span><span class="br0">)</span> <span class="kw1">AND</span> jour <span class="sy0"><=</span><span class="nu0">30</span><span class="br0">)</span> THEN var_result <span class="sy0">=</span> true; <span class="kw1">RETURN</span> var_result; ELSIF <span class="br0">(</span><span class="br0">(</span>mois <span class="sy0">=</span> <span class="nu0">2</span> <span class="kw1">AND</span> <span class="br0">(</span>annee % <span class="nu0">4</span><span class="br0">)</span> <span class="sy0">=</span> <span class="nu0">0</span> <span class="kw1">AND</span> jour <span class="sy0"><=</span><span class="nu0">29</span><span class="br0">)</span> <span class="kw1">OR</span> <span class="br0">(</span>mois <span class="sy0">=</span> <span class="nu0">2</span> <span class="kw1">AND</span> <span class="br0">(</span>annee % <span class="nu0">4</span><span class="br0">)</span> <span class="sy0"><></span> <span class="nu0">0</span> <span class="kw1">AND</span> jour <span class="sy0"><=</span><span class="nu0">28</span><span class="br0">)</span><span class="br0">)</span> THEN var_result <span class="sy0">=</span> true; <span class="kw1">RETURN</span> var_result; ELSE var_result <span class="sy0">=</span> false; <span class="kw1">RETURN</span> var_result; END <span class="kw1">IF</span>; <span class="kw1">RETURN</span> <span class="kw1">NULL</span>; END;   $BODY$ <span class="kw1">LANGUAGE</span> <span class="st0">'plpgsql'</span> VOLATILE COST <span class="nu0">100</span>;</pre> </div>  <h2 class="sectionedit31"><a name="fonction_de_nommage_du_jour_passe_en_parametre" id="fonction_de_nommage_du_jour_passe_en_parametre">Fonction de nommage du jour passé en paramètre</a></h2> <div class="level2"> <pre class="code sql"><span class="kw1">CREATE</span> <span class="kw1">OR</span> <span class="kw1">REPLACE</span> <span class="kw1">FUNCTION</span> day_name<span class="br0">(</span>date<span class="br0">)</span> RETURNS text <span class="kw1">AS</span> $BODY$ DECLARE date_test alias <span class="kw1">FOR</span> $<span class="nu0">1</span>; var_result text; myrec RECORD; BEGIN <span class="kw1">FOR</span> myrec <span class="kw1">IN</span> <span class="kw1">SELECT</span> CASE extract<span class="br0">(</span>isodow <span class="kw1">FROM</span> date_test<span class="br0">)</span> WHEN <span class="nu0">1</span> then <span class="st0">'lundi'</span> WHEN <span class="nu0">2</span> THEN <span class="st0">'mardi'</span> WHEN <span class="nu0">3</span> THEN <span class="st0">'mercredi'</span> WHEN <span class="nu0">4</span> THEN <span class="st0">'jeudi'</span> WHEN <span class="nu0">5</span> THEN <span class="st0">'vendredi'</span> WHEN <span class="nu0">6</span> THEN <span class="st0">'samedi'</span> WHEN <span class="nu0">7</span> THEN <span class="st0">'dimanche'</span> END <span class="kw1">AS</span> jour LOOP var_result:<span class="sy0">=</span>myrec<span class="sy0">.</span>jour; END LOOP; <span class="kw1">RETURN</span> var_result; END; $BODY$ <span class="kw1">LANGUAGE</span> <span class="st0">'plpgsql'</span> VOLATILE COST <span class="nu0">100</span>;</pre> <pre class="code sql">$<span class="sy0">></span> <span class="kw1">SELECT</span> day_name<span class="br0">(</span><span class="st0">'2010-02-07'</span><span class="br0">)</span>; $<span class="sy0">></span> dimanche</pre> </div>  creation_bdd:fonctions Sun, 07 Feb 2010 19:23:59 +0200