Nom de la Table comme paramètre de fonction PostgreSQL

ne faites pas ça.

C'est la réponse. C'est un terrible anti-modèle. Quel but a-t-elle? Si le client connaît la table à laquelle il veut des données, alors SELECT FROM ThatTable ! Si vous avez conçu votre base de données d'une manière telle que cela est nécessaire, vous l'avez probablement mal conçu. Si votre couche d'accès aux données a besoin de savoir si une valeur existe dans une table, il est trivialement facile de faire la partie SQL dynamique dans ce code. Le pousser dans la base de données n'est pas bon.

j'ai une idée: installons un dispositif à l'intérieur des ascenseurs où vous pouvez taper le numéro du plancher que vous voulez. Puis quand vous appuyez sur "Go", il déplace une main mécanique sur le bouton correct pour le plancher désiré et le presse pour vous. Révolutionnaire!

apparemment, ma réponse était trop courte sur l'explication donc je répare ce défaut avec plus de détails.

Je n'avais pas l'intention de me moquer. Mon stupide exemple d'ascenseur était le meilleur dispositif que j'ai pu imaginer pour souligner succinctement les défauts de la technique suggérée dans la question. Cette technique ajoute une couche complètement inutile d'indirection, et déplace inutilement le choix de nom de table d'un espace appelant en utilisant un DSL robuste et bien compris (SQL) dans un hybride en utilisant le code SQL obscur/bizarre côté serveur.

une telle division de la responsabilité par le mouvement de la logique de construction de requête en SQL dynamique rend le code plus difficile à comprendre. Il détruit une convention parfaitement raisonnable (comment une requête SQL choisit quoi sélectionner) au nom du code personnalisé chargé de potentiel d'erreur.

• SQL Dynamique offre la possibilité d'injection SQL qui est difficile à reconnaître dans le front-end de code ou le code principal à l'unité (on doit inspecter pour voir cela).

• les procédures et les fonctions stockées peuvent accéder aux ressources que le propriétaire de SP/fonction a des droits mais pas l'appelant. Pour autant que je comprenne, lorsque vous utilisez du code qui produit du SQL dynamique et l'exécute, la base de données exécute le SQL dynamique sous les droits de l'appelant. Cela signifie que vous ne serez pas en mesure d'utiliser des objets privilégiés du tout, ou vous devez les ouvrir à tous les clients, en augmentant la surface de l'attaque potentielle de données privilégiées. Paramétrage de la fonction SP / au moment de la création pour toujours exécuter en tant qu'utilisateur particulier (dans le serveur SQL), EXECUTE AS ) peut résoudre ce problème, mais rend les choses plus compliquées. Cela exacerbe le risque D'injection SQL mentionné au point précédent, en faisant du SQL dynamique un vecteur d'attaque très alléchant.

• Lorsqu'un développeur doit comprendre ce que fait le code d'application pour le modifier ou corriger un bogue, il trouvera très difficile d'obtenir L'exacte requête SQL exécutée. SQL profiler peut être utilisé, mais cela demande des privilèges spéciaux et peut avoir des effets négatifs sur les performances des systèmes de production. La requête exécutée peut être journalisée par le SP mais cela augmente la complexité sans raison (maintien de nouvelles tables, purge de vieilles données, etc.) et est totalement non-évident. En fait, certaines applications sont conçues de telle sorte que le développeur n'a pas de justificatifs de base de données, de sorte qu'il devient presque impossible pour lui de voir réellement la requête présentée.

• Lorsqu'une erreur se produit, telle que lorsque vous essayez de sélectionner une table qui n'existe pas, vous obtenez un message du type "nom d'objet non valide" dans la base de données. Cela se produira exactement le même si vous composez le SQL dans le dos ou la base de données, mais la différence est, un pauvre développeur qui essaye de dépanner le système doit spelunk un niveau plus profondément dans une autre caverne sous celle où le problème existe réellement, pour creuser dans la procédure wonder-qui fait tout cela et essayer de comprendre ce que le problème est. Les Logs ne montreront pas" Error in GetWidget", il montrera"Error in OneProcedureToRuleThemAllRunner". Cette abstraction va simplement rendre votre système pire .

voici un bien meilleur exemple dans pseudo-C# de changement de noms de table basé sur un paramètre:

string sql = string.Format("SELECT * FROM {0};", escapeSqlIdentifier(tableName));
results = connection.Execute(sql);

tous les défauts que j'ai mentionnés avec l'autre technique sont complètement absents de cet exemple.

il y a juste aucun but, aucun bénéfice, aucune amélioration possible dans la soumission d'un nom de table à une procédure stockée.

dans le code plpgsql, l'instruction EXECUTE doit être utilisée pour les requêtes dans lesquelles les noms de table ou les colonnes proviennent de variables. De même, la construction IF EXISTS (<query>) n'est pas autorisée lorsque query est générée dynamiquement.

Voici votre fonction avec les deux problèmes résolus:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

Le premier n'a pas de "travail" dans le sens que vous l'entendez, il ne fonctionne que dans la mesure où elle ne génère pas d'erreur.

essayez SELECT * FROM quote_ident('table_that_does_not_exist'); , et vous verrez pourquoi votre fonction retourne 1: Le select retourne une table avec une colonne (nommée quote_ident ) avec une ligne (la variable ou dans ce cas particulier table_that_does_not_exist ).

ce que vous voulez faire exigera dynamic SQL, qui est en fait l'endroit que le Les fonctions quote_* sont destinées à être utilisées.

si la question était de tester si la table est vide ou non (id=1), Voici une version simplifiée du proc stocké D'Erwin:

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

si vous voulez que le nom de la table, le nom de la colonne et la valeur soient transmis dynamiquement pour fonctionner comme paramètre

utilisez ce code

create or replace function total_rows(tbl_name text, column_name text, value int)
returns integer as $total$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$total$ language plpgsql;


postgres=# select total_rows('tbl_name','column_name',2); --2 is the value

j'ai la version 9.4 de PostgreSQL et j'utilise toujours ce code:

CREATE FUNCTION add_new_table(text) RETURNS void AS
$BODY$
begin
    execute
        'CREATE TABLE ' ||  || '(
        item_1      type,
        item_2      type
        )';
end;
$BODY$
LANGUAGE plpgsql

et ensuite:

SELECT add_new_table('my_table_name');

ça me fait du bien.

Attention! L'exemple ci-dessus est l'un de ceux qui montre "comment ne pas si nous voulons garder la sécurité pendant l'interrogation de la base de données": P