misc

Lister les tables filles d'une table

On remplacera "element" par la table père, on pourra bien sûr récupérer les informations de la table pg_class (c) ou pg_inherits (i) en fonction des besoins...



SELECT relname

FROM pg_catalog.pg_class c,

     pg_catalog.pg_inherits i

WHERE i.inhparent=(select oid from pg_class where relname='element');

Modifier les droits d'un utilisateur sur toutes les tables d'un schéma.

Ci-dessous une requête qui permet de composer un script permettant d'affecter les droits SELECT sur toutes les tables du schéma public

select 'GRANT SELECT ON '||table_schema||'.'||table_name||' TO mon_user;'

from   information_schema.tables

where  table_type='BASE TABLE'

and    table_schema='public';

Enregistrer le contenu de cette requête dans un fichier et rejouer le script.

Cette requête peut être modifier pour le faire sur plusieurs schéma ou bien ajouter des droits supplémentaires.

Récupérer le résultat d'une requête éxécutée dans une procédure stockée

En php on peut utiliser directement une requête pour obtenir un recordset.
Cependant si l'on veux récupérer un recordset via une procedure stockée -- pour appliquer des traitements sur des paramètres avant d'exécuter la requête par exemple -- on peut créer une fonction équivalent à celle qui suit.

L'exemple renvoie un setof de la variable out nommée myset et qui est definie du type du champs convoité dans la table. (On utilise %TYPE pour recupérer le type du champs).

CREATE OR REPLACE FUNCTION ma_fonction(out myset ma_table.mon_champ_pk%TYPE)

    RETURNS setof integer AS

$BODY$

    DECLARE

    -- On crée une variable de type record , on peut aussi suivant le besoin créer une variable de type %ROWTYPE

     myrecord record;

BEGIN

    FOR myrecord in SELECT mon_pk FROM ma_table loop

        myset:= myrecord.mon_pk;

        RETURN NEXT;

    END LOOP;

    RETURN;

END;

$BODY$

    LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION ma_fonction(out myset demande.demande_pk%TYPE) OWNER TO mon_user;

Pour récupérer l'ensemble des resultat on selectionne la procedure ainsi:

SELECT * FROM  ma_fonction();

Les mots importants sont :
- setof
- record
- %TYPE et %ROWTYPE
- for...loop
- return next

Trouver le 1er élément disponible dans une liste de numériques

On veut parfois trouver le 1er élément disponible dans une liste. C'est souvent le cas quand la clé primaire d'une table est un nombre mais qu'il n'est pas soumis à une séquence.. Ou alors qu'on a des trous dans la séquence et qu'on veut les combler.

Soit la table nombres (a integer, [...]), la requête suivante retourne le 1er élement disponible:

explain

SELECT (x.a+1) AS id_disponible

FROM nombres x

     LEFT JOIN nombres y

     ON ((x.a + 1) = y.a)

WHERE y.a IS NULL

ORDER BY x.a LIMIT 1;

                                    QUERY PLAN

-----------------------------------------------------------------------------------

 Limit  (cost=0.00..1.58 rows=1 width=4)

   ->  Nested Loop Left Join  (cost=0.00..22.09 rows=14 width=4)

         Join Filter: (("outer".a + 1) = "inner".a)

         Filter: ("inner".a IS NULL)

         ->  Index Scan using id_a on nombres x  (cost=0.00..3.15 rows=14 width=4

         ->  Seq Scan on nombres y  (cost=0.00..1.14 rows=14 width=4)

(6 lignes)
test2=> SELECT (x.a+1) AS id_disponible FROM nombres x LEFT JOIN nombres

y ON ((x.a + 1) = y.a) WHERE y.a IS NULL ORDER BY x.a LIMIT 1;

 id_disponible

---------------

             4

(1 ligne)

Si vous constatez des lenteurs, il vous faudra créer un index sur a (si ce n'est pas déjà fait, ce dont je doute):

create unique index id_a on nombres(a);

Et un index fonctionnel comme suit:

create index id_a_suivant on nombres ((a+1));

Merci à John Hansen (appeljack) pour cette idée.