Jak zwrócić wynik SELECT wewnątrz funkcji w PostgreSQL?


106

Mam taką funkcję w PostgreSQL, ale nie wiem jak zwrócić wynik zapytania:

CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
  RETURNS SETOF RECORD AS
$$
BEGIN
    SELECT text, count(*), 100 / maxTokens * count(*)
    FROM (
        SELECT text
    FROM token
    WHERE chartype = 'ALPHABETIC'
    LIMIT maxTokens
    ) as tokens
    GROUP BY text
    ORDER BY count DESC
END
$$
LANGUAGE plpgsql;

Ale nie wiem, jak zwrócić wynik zapytania wewnątrz funkcji PostgreSQL.

Odkryłem, że typem zwrotu powinien być SETOF RECORD, prawda? Ale polecenie powrotu jest nieprawidłowe.

Jaki jest właściwy sposób, aby to zrobić?


Dlaczego je liczysz; czy masz zduplikowane tokeny w TABELI Tokenów? Ponadto: dodaj definicję tabeli do swojego pytania.
wildplasser

1
Czy to cała twoja funkcja? Jeśli nie masz żadnych innych instrukcji w funkcji, po prostu powinieneś to zrobić LANGUAGE SQL.
jpmc26

Odpowiedzi:


135

Zastosowanie RETURN QUERY:

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- also visible as OUT parameter inside function
               , cnt   bigint
               , ratio bigint) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt                 -- column alias only visible inside
        , (count(*) * 100) / _max_tokens  -- I added brackets
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;                    -- potential ambiguity 
END
$func$  LANGUAGE plpgsql;

Połączenie:

SELECT * FROM word_frequency(123);

Wyjaśnienie:

  • O wiele bardziej praktyczne jest jawne zdefiniowanie typu zwracanego niż zwykłe zadeklarowanie go jako rekordu. W ten sposób nie musisz podawać listy definicji kolumn przy każdym wywołaniu funkcji. RETURNS TABLEjest jednym ze sposobów, aby to zrobić. Są inni. Typy danych OUTparametrów muszą dokładnie odpowiadać temu, co jest zwracane przez zapytanie.

  • OUTUważnie wybieraj nazwy parametrów. Są widoczne w korpusie funkcji prawie wszędzie. Kwalifikuj kolumny o tej samej nazwie, aby uniknąć konfliktów lub nieoczekiwanych wyników. Zrobiłem to dla wszystkich kolumn w moim przykładzie.

    Zwróć jednak uwagę na potencjalny konflikt nazw między OUTparametrem cnta aliasem kolumny o tej samej nazwie. W tym konkretnym przypadku ( RETURN QUERY SELECT ...) Postgres używa aliasu kolumny zamiast OUTparametru w obie strony. Może to być jednak niejednoznaczne w innych kontekstach. Istnieje wiele sposobów uniknięcia nieporozumień:

    1. Użyj porządkowej pozycji pozycji na liście SELECT: ORDER BY 2 DESC. Przykład:
    2. Powtórz wyrażenie ORDER BY count(*).
    3. (Nie dotyczy tutaj.) Ustaw parametr konfiguracyjny plpgsql.variable_conflictlub użyj specjalnego polecenia #variable_conflict error | use_variable | use_columnw funkcji. Widzieć:
  • Nie używaj „tekst” ani „liczba” jako nazw kolumn. Oba są legalne w Postgresie, ale „count” jest słowem zastrzeżonym w standardowym SQL, a nazwa funkcji i „tekst” to podstawowy typ danych. Może prowadzić do mylących błędów. Używam txti cntw moich przykładach.

  • Dodano brakujący ;i poprawiono błąd składni w nagłówku. (_max_tokens int), nie (int maxTokens)- wpisz po nazwie .

  • Podczas pracy z dzieleniem całkowitoliczbowym lepiej jest najpierw pomnożyć, a później podzielić, aby zminimalizować błąd zaokrąglania. Jeszcze lepiej: pracuj z numeric(lub typem zmiennoprzecinkowym). Zobacz poniżej.

Alternatywny

To jest to, co ja myślę zapytanie powinno wyglądać w rzeczywistości (obliczenie względnego udziału za żeton ):

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt            text
               , abs_cnt        bigint
               , relative_share numeric) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt, t.cnt
        , round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2)  -- AS relative_share
   FROM  (
      SELECT t.txt, count(*) AS cnt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      GROUP  BY t.txt
      ORDER  BY cnt DESC
      LIMIT  _max_tokens
      ) t
   ORDER  BY t.cnt DESC;
END
$func$  LANGUAGE plpgsql;

Wyrażenie sum(t.cnt) OVER ()jest funkcją okna . Ty mógł użyć CTE zamiast podkwerendzie - ładna, ale podzapytanie jest zazwyczaj tańsze w prostych przypadkach takich jak ten.

Ostateczna jawna RETURNinstrukcja nie jest wymagana (ale dozwolona) podczas pracy z OUTparametrami lub RETURNS TABLE(co powoduje niejawne użycie OUTparametrów).

round()z dwoma parametrami działa tylko dla numerictypów. count()w podzapytaniu daje bigintwynik, a sum()over to bigintdaje numericwynik, więc mamy do czynienia z numericliczbą automatycznie i wszystko po prostu układa się na swoim miejscu.


Bardzo dziękuję za odpowiedź i poprawki. Teraz działa dobrze (zmieniłem tylko typ współczynnika na numeryczny).
Renato Dinhani

@ RenatoDinhaniConceição Cool! Dodałem wersję, która może, ale nie musi, odpowiadać na dodatkowe pytanie, którego tak naprawdę nie zadałeś. ;)
Erwin Brandstetter

Fajnie, jedyną rzeczą jest to, że myślę, że potrzebujesz RETURN;wcześniej END;, przynajmniej ja tak zrobiłem - ale robię UNION, więc nie jestem pewien, czy to sprawia, że ​​jest inaczej.
yekta

@yekta: Dodałem kilka informacji dotyczących roli RETURN. Naprawiono niepowiązany błąd i dodano kilka ulepszeń podczas pracy.
Erwin Brandstetter

1
Jaki jest sposób, aby to zrobić, jeśli nie chcesz ograniczać tego, co jest w funkcji Return TABLE (). IE TABELA ZWROTNA (*)?
Nick

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.