Forums PostgreSQL.fr

Michel Depiesse

Membre

nom de schema comme argument d'une fonction

Bonjour,

J'aimerais écrire dans le schema public une procédure qui va chercher ses données dans un autre schéma de la db. Le nom du schema est donc un parmètre de la fonction. Mais je ne peux pas tout simplement écrire :

create function sync_client2server(schema_name varchar) returns void as $$
declare
        r       record;
begin

        for r in select * from schema_name.audit_history loop
            ...
        end loop;

end
$$ language 'plpgsql'

Que faire ?

Merci pour votre attention

ruizsebastien

Membre

Re : nom de schema comme argument d'une fonction

Bonjour,

Si ça peut vous aider voici un exemple d'une procédure qui calcul les stats d'un schéma :

CREATE OR REPLACE FUNCTION toto.calcul_stats_schema_full(p_schema character varying) RETURNS integer AS $BODY$
DECLARE
  crs_tab CURSOR IS
         select tablename 
         FROM pg_tables
         WHERE schemaname = p_schema;
 
  nametab varchar;
   
BEGIN
   
         OPEN crs_tab;

      LOOP
         FETCH crs_tab INTO nametab;
         EXIT WHEN NOT FOUND;
         RAISE NOTICE 'Calcul des stats de la table %',nametab;
         execute 'analyze verbose '||p_schema||'.'||nametab;

       END LOOP;
     
           CLOSE crs_tab;
           RAISE NOTICE 'Analyse des tables du schema % OK',p_schema;
    return 0;

EXCEPTION
WHEN others THEN
RAISE NOTICE 'probleme analyse table  --- % ---, Code_err = % ',nametab,sqlerrm ;
return -1;
END;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100;

---

Cordialement,

Sébastien.

dverite

Membre

Re : nom de schema comme argument d'une fonction

Il suffit de remplacer

  for r in select * from schema_name.audit_history loop

par l'équivalent avec du SQL dynamique:

 for r in execute format('select * from %I.audit_history', schema_name) loop 

Pour la doc sur FOR LOOP, voir http://doc.postgresql.fr/9.6/plpgsql-co … tures.html

Dernière modification par dverite (13/02/2017 21:00:56)

---

@DanielVerite
http://blog-postgresql.verite.pro/

Michel Depiesse

Membre

Re : nom de schema comme argument d'une fonction

Merci à tous les deux !

Avec une préférence pour la seconde solutuin;

Michel Depiesse

Membre

Re : nom de schema comme argument d'une fonction

mais ça ne marche pas !

quand je fais :

declare
        r       audit_history%ROWTYPE;
begin
        for r in execute format('select * from %I.audit_history',schema_name) loop
.../...

je reçois

psql:sync_client2server.sql:92: ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

Il va de soi que la table audit_history a la même structure dans les 2 schemas.

Merci pour votre attention et votre patience

ruizsebastien

Membre

Re : nom de schema comme argument d'une fonction

remplacez "execute" par "perform"

Dernière modification par ruizsebastien (14/02/2017 16:06:15)

---

Cordialement,

Sébastien.

Michel Depiesse

Membre

Re : nom de schema comme argument d'une fonction

ca va non plus...

je reçois le même message d'horreur

Michel Depiesse

Membre

Re : nom de schema comme argument d'une fonction

j'ai contourné le problème

merci

ruizsebastien

Membre

Re : nom de schema comme argument d'une fonction

tant mieux.

N'hésitez pas à dire comment vous avez contourné le problème, ça pourrait aider quelqu'un d'autre.

---

Cordialement,

Sébastien.

Michel Depiesse

Membre

Re : nom de schema comme argument d'une fonction

effectivement !

create or replace function sync_client2server(schema_name varchar) returns void as $$
declare
        r       audit_history%ROWTYPE;
begin


        for r in execute format('select * from %I.audit_history',schema_name) loop


                case r.operation

                        when 'INSERT' then perform sync_insert(schema_name, r.*);

                        when 'UPDATE' then perform sync_update(schema_name, r.*);

                        when 'DELETE' then perform sync_delete(schema_name, r.*);

                end case;

        end loop;

end $$ language 'plpgsql';

execute pour le curseur, perform pour le call des fonctions et bonne chance à tous !
Encore merci à vous