Comment puis-je implémenter efficacement les clauses ORDER BY et LIMIT paramétrées dans les fonctions de table PostgreSQL ?

Ordre paramétré par PostgreSQL par/limite dans la fonction de table

L'utilisation des fonctions de table dans PostgreSQL permet une manipulation et une récupération efficaces des données. Cependant, lorsqu'il s'agit de grands ensembles de données, il devient crucial d'appliquer le tri et la pagination pour gérer efficacement les résultats.

Considérons le scénario suivant : nous avons une fonction de table, getStuff, qui effectue une simple requête SELECT sur une table nommée stuff :

CREATE OR REPLACE FUNCTION getStuff(param character varying)
  RETURNS SETOF stuff AS
$BODY$
    select *
    from stuff
    where col = 
$BODY$
  LANGUAGE sql;

Initialement, nous invoquons la fonction comme suit :

select * from getStuff('hello');

Cependant, pour optimiser les performances, nous devons appliquer une clause ORDER BY et une clause LIMIT. Malheureusement, l'exécution d'une requête comme celle-ci :

select * from getStuff('hello') order by col2 limit 100;

récupèrera toutes les lignes de la table stuff, puis les ordonnera et les limitera, ce qui entraînera un traitement inefficace.

De plus, PostgreSQL ne fournit pas de méthode inhérente pour passer les arguments ORDER BY à une fonction du langage SQL, car seules les valeurs peuvent être transférées. Construire la requête au sein d'une fonction plpgsql et l'exécuter via EXECUTE est une approche alternative, mais ce n'est pas la solution la plus élégante.

La solution :

Heureusement, une fonction plpgsql offre une solution efficace pour ce scénario. Nous pouvons modifier la fonction getStuff pour accepter des paramètres supplémentaires pour ORDER BY et LIMIT :

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying, _limit int)
  RETURNS SETOF stuff AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
      SELECT *
      FROM   stuff
      WHERE  col = 
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  '
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

Maintenant, nous pouvons invoquer la fonction avec les paramètres souhaités :

SELECT * FROM getStuff('hello', 'col2', 100);

Le RETURN QUERY EXECUTE L'instruction récupère efficacement les résultats de la requête dans la fonction.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!