Fonctions avec un nombre variable de paramètres d'entrée

Je suis en création d'une procédure stockée (fonction) dans un PostgreSQL DB, ce qui met à jour une table en fonction de son entrée. Dans le but de créer un nombre variable de paramètre de la fonction, je suis de la création d'un supplément de paramètre d'entrée appelé mode, que j'utilise pour contrôler les paramètres que j'utilise sur la requête de mise à jour.

CREATE OR REPLACE FUNCTION update_site(
    mode integer,
    name character varying,
    city character varying,
    telephone integer,
)
RETURNS integer AS
$$
BEGIN
IF mode = 0 THEN
BEGIN
    UPDATE "Sites" SET 
    ("City","Telephone") = (city,telephone)
    WHERE "SiteName" = name;
    RETURN 1;
    EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
END;
ELSIF mode = 1 THEN
BEGIN
    UPDATE "Sites" SET "City" = city
    WHERE "SiteName" = name;
    RETURN 1;
    EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
END;
    ELSIF mode = 2 THEN
BEGIN
    UPDATE "Sites" SET "Telephone" = telephone
    WHERE "SiteName" = name;
    RETURN 1;
    EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
END;
    ELSE
            RAISE NOTICE 'Error on site update: %, %',SQLERRM,SQLSTATE;
    RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

Ce qui serait le mieux? Pour créer une fonction update_site(<all the columns of table>) et une fonction distincte update_site(id integer, <varchar column to update>), ou utiliser le mode en une seule fonction pour définir la différence? Quelle option est la plus efficace? Une fonction unique ou différentes pour chaque but?

OriginalL'auteur anairinac | 2013-05-28

  1. 7

    De fonctionnalités avancées comme VARIADIC ou même polymorphes types d'entrée et SQL dynamique sont très puissants. Le dernier chapitre de cette réponse fournit un exemple avancé:

    Mais pour un cas simple comme le vôtre, vous pouvez simplement utiliser de valeurs par défaut pour les paramètres de la fonction. Tout dépend des exigences précises.

    Si les colonnes en question sont tous définis NOT NULL, ce serait probablement plus simple et plus rapide:

    CREATE OR REPLACE FUNCTION update_site(
     _name      text             -- always required, so no default
   , _city      text DEFAULT NULL
   , _telephone integer DEFAULT NULL)
  RETURNS integer AS
$func$
BEGIN
   IF  _city IS NULL
   AND _telephone IS NULL THEN
      RAISE WARNING 'At least one value to update required!';
      RETURN;  -- nothing to update
   END IF;

   UPDATE "Sites"
   SET    "City"      = COALESCE(_city, "City")
        , "Telephone" = COALESCE(_telephone, "Telephone")
   WHERE  "SiteName" = _name;
END
$func$  LANGUAGE plpgsql;

    Assurez-vous de lire sur les valeurs par défaut dans le manuel!

    Pour éviter les conflits de noms entre les paramètres et les noms de colonne-je prendre l'habitude de faire précéder les paramètres d'entrée avec _. C'est une question de goût et de style.

    • Le premier paramètre name n'a pas de défaut, car il est nécessaire à tout moment.
    • D'autres paramètres peuvent être omis.
    • Au moins un est requis, ou un WARNING est soulevé et rien ne se passe.
    • La UPDATE ne changera des colonnes pour les paramètres donnés.
    • Peut facilement être étendu pour N paramètres.

    Appel de fonction

    Pour Postgres 9.5 ou plus récent:

    La manière la plus simple est avec notation de position pour les paramètres. Cela ne vous permet d'omettre la plus à droite du paramètre(s):

    SELECT update_site('foo', 'New York');  -- no telephone

    Nommé notation permet d'omettre tout paramètre qui a une valeur par défaut:

    SELECT update_site(name => 'foo', _telephone => 123);  -- no city

    Les deux peuvent être combinés dans mixte de la notation:

    SELECT update_site('foo', _telephone => 123);  -- still no city

    Avant Postgresql 9.4 ou plus, := a été utilisé pour l'affectation à l'appel:

    SELECT update_site(name := 'foo', _telephone := 123);
SELECT update_site('foo', _telephone := 123);

    Toujours valable dans Postgres 11 pour des raisons de compatibilité ascendante, mais plutôt de ne pas l'utiliser dans les versions actuelles.

    Merci pour votre réponse. Cela me semble une très bonne solution pour ma question. Ne des valeurs par défaut peut être utilisé sur d'autres bases de données, langages de programmation, autre que PostgreSQL?
    Ces règles s'appliquent aux fonctions PostgreSQL. D'autres SGBDR ont intégré les langages procéduraux, trop. Oracle PL/SQL est le plus proche, PL/pgSQL a été calquée sur celle. Vous ne savez pas comment PL/SQL ou MS tSQL gérer les valeurs par défaut. Le concept de valeurs par défaut est sans doute plus large que VARIADIC ou polymorphes paramètres.
    J'ai décidé de changer mes accepté de répondre à celui-ci en raison de sa plus détaillée dans la réponse à ma question.
    Sympa... j'ai réussi à rater le soutien pour les paramètres par défaut entièrement.

    OriginalL'auteur Erwin Brandstetter

  2. 3

    Il ya quelques choses que vous aurez envie de regarder dans:

    • Dynamiquement la construction du SQL à l'aide de la format fonction et de son %I et %L prescripteurs, puis l'exécuter avec EXECUTE ... USING; et

    • À l'aide de VARIADIC paramètres à prendre un nombre variable d'arguments à la fonction, avec la réserve qu'elles doivent toutes être du même type de données.

    Je vous remercie pour votre réponse. J'ai trouvé des informations sur EXÉCUTER format, et d'accomplir ce que j'ai demandé à des paramètres variables, mais depuis EXECUTE est recalculé à chaque fois que la fonction est exécutée, je suppose que, finalement, il serait moins efficace que les fonctions normales. Je voulais savoir si c'était possible, mais pour ce que je me demande c'est plus que nécessaire. De toute façon, merci. Je vais certainement lire plus à ce sujet, cependant. Semble intéressant.
    Yep, c'est un utile facilility, mais attention à l'utilisation EXECUTE ... USING ou la sécurité les paramètres de format pour éviter les blessures accidentelles SQL injection de l'exposition par le biais de la base de données SQL dynamique.

    OriginalL'auteur Craig Ringer