Forums PostgreSQL.fr

Gildas

Membre

[PL/PGSQL] Fonction qui crée une fonction, gestion des Dollars...

Bonjour,

J'ai de nombreuses bases de versions différentes, que je dois mettre a jour, la mise a jour se fait via l'exécution d'une fonction PlPgsql seulement si la version de la base est antérieure à la version du patch, ceci assure donc que les bases mises à jour aient tous les patchs passés, et ceci quelque soit la version.

Ex : J'ai 3 patchs a passer sur mes bases...
     - Base a en version 1.0 -> passage des patchs 1, 2 et 3
     - Base b en version 1.0 patch 2 -> passage du patch 3 uniquement
     - Base c en version 1.0 patch 3 -> passage d'aucun patch
     - Base d en version 1.0 patch 1 -> passage des patchs 2 et 3

J'espere que c'est clair désormais...

Bon maintenant, je vais exposer le réel problème rencontré. Le patch est réalisé via une fonction PlPgsql, qui elle meme peut mettre a jour ou créer des fonctions PlPgsql... Les fonctions Plpgsql contentant des paramètres ne sont pas gérables du fait que le Dollar ($) utilisé pour les parametres dans les fonctions ne passe pas lors de l'exécution du patch...

Coment utiliser le Dollar dans une fonction PlPgsql qui se trouve dans une fonction PlPgsqL ?

Comment reproduire la chose ?

Tout d'abord créer la table permettant de gérer le versionning...

CREATE TABLE versions
(
  id_version character varying(16) NOT NULL,
  commands character varying,
  "number" integer,
  CONSTRAINT versions_pkey PRIMARY KEY (id_version)
)
WITH (OIDS=FALSE);

INSERT INTO versions(id_version, commands, "number") VALUES ('1.0.3211', '', 3211);

On voit que cette bases est versionnée 1.0.3211

Ensuite essayer de passer le patch 1.0.3221... bonne chance...

---------------------------------------------------------
---                DROP PREVIOUS PATCH                ---
---------------------------------------------------------
DROP FUNCTION IF EXISTS PatchDatabase();

---------------------------------------------------------
---                   PREPARE PATCH                   ---
---------------------------------------------------------
CREATE FUNCTION PatchDatabase() RETURNS void AS
$$
DECLARE
  record RECORD;
  recordVersion RECORD;
  
  BUILD_VERSION integer :=      3321;
  FULL_VERSION  varchar := '1.0.3321';
  
  BEGIN	
    SELECT * INTO record FROM versions WHERE "number">=BUILD_VERSION;
    IF record IS NULL THEN
      BEGIN
	    CREATE OR REPLACE FUNCTION my_function(channelid integer)
          RETURNS SETOF record AS
        $BODY$
        DECLARE
            chain_key INTEGER;
            chain_id INTEGER;
            record RECORD;
        BEGIN
            FOR chain_id IN SELECT id_acq_chain FROM channels, acq_chains WHERE channels.key_acq_chain = acq_chains.key_acq_chain and id_channel=$1 ORDER BY channels.date_modif, acq_chains.date_modif DESC LIMIT 1
            LOOP
                SELECT * INTO record FROM acq_chains WHERE id_acq_chain=chain_id ORDER BY date_modif DESC LIMIT 1;
                RETURN NEXT record;
           END LOOP;
            RETURN;
        END
        $BODY$
          LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER COST 100 ROWS 1;

	    RETURN;
	    EXCEPTION WHEN unique_violation THEN
	  END;
    END IF;
  END;
$$
LANGUAGE 'plpgsql';

---------------------------------------------------------
---                   LAUNCH PATCH                    ---
---------------------------------------------------------
SELECT * FROM PatchDatabase();

Le résultat de l'exécution de ce patch est

ERROR: syntax error at or near "$1"
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "patchdatabase" near line 28

Donc pas top... Comment utiliser le dollar ?

D'avance Merci...

Gildas

Membre

Re : [PL/PGSQL] Fonction qui crée une fonction, gestion des Dollars...

C'est bon j'ai trouvé l'erreur...

En fait le message renvoyé n'est pas trop clair, je me suis attardé sur le fameux '$1' de ma fonction alors que le problème vient de la variable record de la fonction PatchDatabase qui est aussi utilisée dans la fonction my_function.

Donc on a deux fois la même variable, pas bien... J'ai donc renommé la variable de la fonction PatchDatabase et cela fonctionne...

Désolé !

gleu

Administrateur

Re : [PL/PGSQL] Fonction qui crée une fonction, gestion des Dollars...

Pour ce que ça vaut, j'ai corrigé la fonction en incluant le code dans une variable de type text. Ça me donne ceci :

CREATE FUNCTION PatchDatabase() RETURNS void AS
$$
DECLARE
  record RECORD;
  recordVersion RECORD;

  create_my_function text;
  
  BUILD_VERSION integer :=      3321;
  FULL_VERSION  varchar := '1.0.3321';
  
  BEGIN    
    SELECT * INTO record FROM versions WHERE "number">=BUILD_VERSION;
    IF record IS NULL THEN
      BEGIN
        create_my_function := $create_my_function$
        CREATE OR REPLACE FUNCTION my_function(channelid integer)
          RETURNS SETOF record AS
        $BODY$
        DECLARE
            chain_key INTEGER;
            chain_id INTEGER;
            record RECORD;
        BEGIN
            FOR chain_id IN SELECT id_acq_chain FROM channels, acq_chains
WHERE channels.key_acq_chain = acq_chains.key_acq_chain and id_channel=$1
ORDER BY channels.date_modif, acq_chains.date_modif DESC LIMIT 1
            LOOP
                SELECT * INTO record FROM acq_chains WHERE
id_acq_chain=chain_id ORDER BY date_modif DESC LIMIT 1;
                RETURN NEXT record;
           END LOOP;
            RETURN;
        END
        $BODY$
          LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER COST 100 ROWS 1
        $create_my_function$;
        EXECUTE create_my_function;

        RETURN;
        EXCEPTION WHEN unique_violation THEN
      END;
    END IF;
  END;
$$
LANGUAGE 'plpgsql';

et ça fonctionne pour moi.

---

Guillaume.

Gildas

Membre

Re : [PL/PGSQL] Fonction qui crée une fonction, gestion des Dollars...

Pour ce que ça vaut, j'ai corrigé la fonction en incluant le code dans une variable de type text. Ça me donne ceci :

....

et ça fonctionne pour moi.

Merci de t'être penché sur mon problème, mais le renommage du premier record a suffit a résoudre mon problème.