Jak stworzyć tymczasową funkcję w PostgreSQL?


83

Muszę wykonać pętlę w bazie danych. Jest to wymóg jednorazowy. Po wykonaniu funkcji porzucam teraz funkcję.

Czy istnieje dobre podejście do tworzenia funkcji tymczasowych / jednorazowych?

Odpowiedzi:


115

Musiałem wiedzieć, jak wykonać wielokrotne użycie w scenariuszu, który pisałem. Okazuje się, że możesz utworzyć tymczasową funkcję za pomocą schematu pg_temp. To jest schemat, który jest tworzony na żądanie dla twojego połączenia i tam są przechowywane tymczasowe tabele. Po zamknięciu lub wygaśnięciu połączenia ten schemat jest odrzucany. Okazuje się, że jeśli utworzysz funkcję na tym schemacie, schemat zostanie utworzony automatycznie. W związku z tym,

create function pg_temp.testfunc() returns text as 
$$ select 'hello'::text $$ language sql;

będzie funkcją, która będzie się utrzymywać tak długo, jak długo utrzymuje się połączenie. Nie ma potrzeby wywoływania polecenia drop.


62

Kilka dodatkowych uwag do inteligentnej sztuczki w odpowiedzi @ crowmagnumb :

  • Funkcja musi być zawsze kwalifikowana do schematu , nawet jeśli pg_tempznajduje się w search_path(tak jak jest to domyślnie), według Tom Lane, aby zapobiec koniom trojańskim:
CREATE FUNCTION pg_temp.f_inc(int)
  RETURNS int AS 'SELECT $1 + 1' LANGUAGE sql IMMUTABLE;

SELECT pg_temp.f_inc(42);
f_inc
-----
43
  • Funkcja utworzona w schemacie tymczasowym jest widoczna tylko w tej samej sesji (podobnie jak tabele tymczasowe). Jest niewidoczny dla wszystkich innych sesji (nawet dla tej samej roli). Państwo mogli uzyskać dostęp do funkcji w innej roli w tej samej sesji po SET ROLE.

  • Możesz nawet utworzyć indeks funkcjonalny oparty na tej funkcji „temp”:

    CREATE INDEX foo_idx ON tbl (pg_temp.f_inc(id));
    

    W ten sposób tworząc zwykły indeks przy użyciu funkcji tymczasowej w tabeli innej niż tymczasowa. Taki indeks byłby widoczny dla wszystkich sesji, ale nadal ważny tylko dla sesji tworzącej. Planer zapytań nie będzie używał indeksu funkcjonalnego, jeśli wyrażenie nie jest powtarzane w zapytaniu. Wciąż trochę brudna sztuczka. Zostanie automatycznie usunięty po zamknięciu sesji - jako obiekt zależny. Wydaje się, że nie powinno to być w ogóle dozwolone ...


Jeśli potrzebujesz tylko wielokrotnie wykonywać funkcję i potrzebujesz tylko SQL, zamiast tego rozważ przygotowaną instrukcję . Działa podobnie do tymczasowej funkcji SQL, która umiera pod koniec sesji. Jednak to nie to samo i może być używane tylko samodzielnie z EXECUTE, a nie zagnieżdżone w innym zapytaniu. Przykład:

PREPARE upd_tbl AS
UPDATE tbl t SET set_name = $2 WHERE tbl_id = $1;

Połączenie:

EXECUTE upd_tbl(123, 'foo_name');

Detale:


31

Jeśli używasz wersji 9.0, możesz to zrobić za pomocą nowej instrukcji DO:

http://www.postgresql.org/docs/current/static/sql-do.html

W przypadku poprzednich wersji musisz utworzyć funkcję, wywołać ją i ponownie upuścić.


4
... Przydaje się do tworzenia skryptów na terminalu, ale nie można jej wywołać ponownie, jak "funkcja anonimowa" (lub lambda), więc instrukcja DO nie jest tak użyteczna jak "funkcja tymczasowa".
Peter Krauss,

@PeterKrauss: jeśli chcesz wywołać to ponownie, musisz stworzyć prawdziwą funkcję.
a_horse_with_no_name

Oczywiście mój a_hourse :-) Pokazałem tylko teoretyczną ścieżkę implementacji „tymczasowej”… Dlatego lepszą odpowiedzią (możliwą z PostgreSQL) na główne pytanie jest pg_temp.foo(). Nie rozumiem, dlaczego (!?) Dzisiaj, 2014, przy tak prostych i szybkich przykładach jak Lua , języki SQL DML nie oferują funkcji lambda (!).
Peter Krauss,

7
Ponadto DOinstrukcje nie mogą mieć parametrów wejściowych i nie mogą zwracać wyniku, w przeciwieństwie do funkcji.
Daniel Vérité

2
Jeśli nie zwraca, czy powinniśmy nazwać to „funkcją”?
AndreKR

-4

W przypadku procedur ad hock kursory nie są takie złe. Są jednak zbyt nieefektywne do użytku w produktach.

Pozwolą ci łatwo zapętlić wyniki sql w db.


6
Jak myślisz, dlaczego kursory są nieefektywne w PostgreSQL?
Frank Heikens,

2
Kursory utrzymują połączenie z bazą danych podczas pętli. Strona internetowa z setkami długo działających kursorów pozbawi połączenia i doprowadzi witrynę / bazę danych na kolana.
Byron Whitlock
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.