Retour SETOF lignes de PostgreSQL fonction

J'ai une situation où je veux retourner à la jointure entre les deux points de vue. et c'est un grand nombre de colonnes. C'était assez facile dans sql server. Mais dans PostgreSQL, quand je fais la jointure. Je reçois le message d'erreur "une colonne d'une liste de définition est nécessaire".

Est-il possible que je peux contourner cela, je ne veux pas donner les définitions de retour colonnes. 

CREATE OR REPLACE FUNCTION functionA(username character varying DEFAULT ''::character varying, databaseobject character varying DEFAULT ''::character varying)
  RETURNS SETOF ???? AS
$BODY$
Declare 
SqlString varchar(4000) = '';
BEGIN
IF(UserName = '*') THEN
   Begin
   SqlString  := 'select * from view1 left join ' + databaseobject  + ' as view2 on view1.id = view2.id';
   End;
ELSE
    Begin
    SqlString := 'select * from view3 left join ' + databaseobject  + ' as view2 on view3.id = view2.id';
    End;
END IF; 
execute (SqlString  );
END;
$BODY$

OriginalL'auteur user433023 | 2013-07-25

  1. 27

    Désinfecter fonction

    Le manuel a toutes les bases pour PL/pgSQL. Fondamentalement, ce que vous avez peut être simplifié /aseptisé pour:

    CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS ???? AS
$func$
BEGIN

RETURN QUERY EXECUTE
format ('SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)'
       , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END, databaseobject);

END
$func$  LANGUAGE plpgsql;

    • Vous n'avez pas besoin d'instances supplémentaires de COMMENCER .. FIN dans le corps de la fonction sauf pour démarrer un code de bloc avec son propre champ d'application, ce qui est rarement nécessaire.

    • La norme SQL opérateur de concaténation est ||. + est un "créatif" plus de votre ancien fournisseur.

    • Ne pas utiliser À dos de chameau cas identifiants, sauf si vous double-quote. Mieux que vous ne les utilisez pas à tous:

    • varchar(4000) est également adapté à une limitation spécifique de SQL Server. Ce type de données n'a pas d'avantage en termes de performances que ce soit dans Postgres. À n'utiliser que si vous avez réellement besoin d'une limite de 4000 caractères. Je voudrais juste utiliser texte - sauf que nous n'avons pas besoin des variables à tous les ici, après, j'ai simplifié la fonction.

    • Si vous n'avez pas utilisé format(), pourtant, consulter le mode d'emploi ici.

    Type de retour

    Maintenant, pour votre question réelle: Le type de retour d'une requête dynamique est un peu délicat, car SQL requiert la fonction pour retourner un type défini. Si vous avez une table ou de la vue ou de type composite dans votre base de données déjà qui correspond à la définition de la colonne de la liste que vous souhaitez retourner, vous pouvez utiliser:

    CREATE FUNCTION foo()
  RETURNS SETOF my_view AS
...

    Si vous êtes le type comme vous allez, vous pouvez soit retourner anonyme des dossiers:

    CREATE FUNCTION foo()
  RETURNS SETOF record AS
...

    ou de fournir une définition de colonne de la liste (la plus simple) RETURNS TABLE:

    CREATE FUNCTION foo()
  RETURNS TABLE (col1 int, col2 text, ...) AS
...

    L'inconvénient pour les enregistrements: vous devez fournir une définition de colonne de la liste à chaque appel, de sorte que je n'ai pratiquement jamais l'utiliser.

    Je ne voudrais pas utiliser SELECT * pour commencer. L'utilisation d'une liste définitive des colonnes de retour et de déclarer votre type de retour en conséquence:

    CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS TABLE(col1 int, col2 text, col3 date) AS
$func$
BEGIN

RETURN QUERY EXECUTE
format ('SELECT v1.col1, v1.col2, v2.col3
         FROM %s v1 LEFT JOIN %I v2 USING (id)$f$
       , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END, databaseobject);

END
$func$;

    Entièrement pour les requêtes dynamiques, je préfère utiliser une simple requête SQL pour commencer. Pas une fonction.

    Il y a des options plus avancées, mais vous pourriez avoir besoin pour étudier les bases en premier.

    OriginalL'auteur Erwin Brandstetter