PostgreSQL - Écrire du sql dynamique dans une procédure stockée qui renvoie un jeu de résultats

Comment puis-je écrire une procédure stockée qui contient un construit dynamiquement instruction SQL qui retourne un jeu de résultats? Voici mon exemple de code:

CREATE OR REPLACE FUNCTION reporting.report_get_countries_new (
  starts_with varchar,
  ends_with varchar
)
RETURNS TABLE (
  country_id integer,
  country_name varchar
) AS
$body$
DECLARE
  starts_with ALIAS FOR $1;
  ends_with ALIAS FOR $2;
  sql VARCHAR;
BEGIN

    sql = 'SELECT * FROM lookups.countries WHERE lookups.countries.country_name >= ' || starts_with ;

    IF ends_with IS NOT NULL THEN
        sql = sql || ' AND lookups.countries.country_name <= ' || ends_with ;
    END IF;

    RETURN QUERY EXECUTE sql;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

Ce code renvoie une erreur:

ERROR:  syntax error at or near "RETURN"
LINE 1: RETURN QUERY SELECT * FROM omnipay_lookups.countries WHERE o...
        ^
QUERY:  RETURN QUERY SELECT * FROM omnipay_lookups.countries WHERE omnipay_lookups.countries.country_name >= r
CONTEXT:  PL/pgSQL function "report_get_countries_new" line 14 at EXECUTE statement

J'ai essayé d'autres façons au lieu de cela:

RETURN QUERY EXECUTE sql;

Voie 1:

RETURN EXECUTE sql;

Chemin 2:

sql = 'RETURN QUERY SELECT * FROM....
/*later*/
EXECUTE sql;

Dans tous les cas, sans succès.

En fin de compte, je veux écrire une procédure stockée qui contient une instruction sql dynamique et qui retourne le résultat de l'instruction sql dynamique.

Postgres' version?

OriginalL'auteur prince | 2012-08-14

27

Il y a place pour des améliorations:
```
CREATE OR REPLACE FUNCTION report_get_countries_new (starts_with text
                                                   , ends_with   text = NULL)
  RETURNS SETOF lookups.countries AS
$func$
DECLARE
   sql text := 'SELECT * FROM lookups.countries WHERE country_name >= $1';
BEGIN
   IF ends_with IS NOT NULL THEN
      sql := sql || ' AND country_name <= $2';
   END IF;

   RETURN QUERY EXECUTE sql
   USING starts_with, ends_with;
END
$func$ LANGUAGE plpgsql;
-- the rest is default settings
```
Les points principaux
- PostgreSQL 8.4 introduit la USING clause de EXECUTE, ce qui est utile pour plusieurs raisons. Récapitulation dans le manuel:
  
  La chaîne de commande peut utiliser les valeurs de paramètres, qui sont référencés dans
  la commande comme $1, $2, etc. Ces symboles, reportez-vous aux valeurs fournies dans les
  le USING clause. Cette méthode est souvent préférable à l'insertion de données
  les valeurs dans la chaîne de commande sous forme de texte: il évite le temps d'exécution de la surcharge de
  la conversion des valeurs de texte et de l'arrière, et il est beaucoup moins sujettes à
  Les attaques par injection SQL puisqu'il n'est pas besoin de citer ou de s'échapper.
  
  OIE, il est plus sûr et plus rapide que la construction d'une chaîne de requête avec le texte de la représentation des paramètres, même lorsque désinfectés avec quote_literal().
  
  Notez que $1, $2 dans la chaîne de requête, reportez-vous aux valeurs fournies dans le USING clause, pas pour les paramètres de la fonction.
- Lorsque vous revenez SELECT * FROM lookups.countries, vous pouvez simplifier le RETURN déclaration comme démontré:
```
RETURNS SETOF lookups.countries
```
  Dans PostgreSQL, il y a un type composite défini pour chaque table automatiquement. L'utiliser. L'effet est que la fonction dépend du type et vous obtenez un message d'erreur si vous tentez de modifier le tableau. Drop & recréer la fonction dans un tel cas.
  
  Cela peut ou peut ne pas être souhaitable - généralement il est! Vous souhaitez être mis au courant des effets secondaires si vous modifier les tables. La façon dont vous l'avez, votre fonction serait de briser le silence et lever une exception sur le prochain appel.
- Si vous fournissez un par défaut explicite pour le deuxième paramètre dans la déclaration, comme l'a démontré, vous pouvez (mais ne pas le faire) simplifier l'appel dans le cas où vous ne souhaitez pas définir une limite supérieure avec ends_with.
```
SELECT * FROM report_get_countries_new('Zaire');
```
  au lieu de:
```
SELECT * FROM report_get_countries_new('Zaire', NULL);
```
  Être conscient de surcharge de fonctions dans ce contexte.
- Ne cite pas le nom de la langue 'plpgsql' même si c'est toléré (pour l'instant). C'est un identificateur.
- Vous pouvez affecter une variable au moment de la déclaration. Enregistre une étape supplémentaire.
- Paramètres sont nommés dans l'en-tête. Baisse de l'absurde lignes:
```
 starts_with ALIAS FOR $1;
 ends_with ALIAS FOR $2;
```
Ce sujet de l'utilisation de quote_literal comme suggéré par Frank Heikens? Est-il plus pertinent?
quote_literal() n'est pas applicable avec USING, ce qui évite la conversion en texte littéral et le besoin de citer tout à fait. Il est dans mon explication.
existe-il des nouvelles considérations pour PostgreSQL 9.3?
C'est tout bon jusqu'à la version actuelle 9.4. Liées à répondre avec plus de détails: stackoverflow.com/questions/17353445/...
belle, merci!

OriginalL'auteur Erwin Brandstetter

Utilisation quote_literal() pour éviter L'injection SQL (!!!) et de réparer vos citant problème:

CREATE OR REPLACE FUNCTION report_get_countries_new (
  starts_with varchar,
  ends_with varchar
)
RETURNS TABLE (
  country_id integer,
  country_name varchar
) AS
$body$
DECLARE
  starts_with ALIAS FOR $1;
  ends_with ALIAS FOR $2;
  sql VARCHAR;
BEGIN

    sql := 'SELECT * FROM lookups.countries WHERE lookups.countries.country_name ' || quote_literal(starts_with) ;

    IF ends_with IS NOT NULL THEN
        sql := sql || ' AND lookups.countries.country_name <= ' || quote_literal(ends_with) ;
    END IF;

    RETURN QUERY EXECUTE sql;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

C'est testé dans la version 9.1, fonctionne très bien.

Le manque de citant était la question. L'erreur renvoyée est trompeuse me faisant penser que l'instruction SQL n'a pas été pris en charge (RETOUR de la REQUÊTE avant de l'EXÉCUTER) et non pas que c'était mal écrit. Merci pour le quote_literal pointe. Les données d'entrée a été corrigée au niveau PHP.

OriginalL'auteur Frank Heikens

Vous devez vous connecter pour publier un commentaire.

Les points principaux