Dynamiczny SQL i RETURN typ

^{(najlepsze zostawiłem na koniec, Czytaj dalej!)}
Chcesz wykonać dynamiczny SQL. Zasadniczo jest to proste w plpgsql przy pomocy EXECUTE. Nie potrzebujesz kursora - w rzeczywistości przez większość czasu lepiej jest ci bez wyraźnych kursorów.
Znajdź przykłady NA SO za pomocą wyszukiwania .

Problem, który napotkasz: chcesz zwrócić rekordy jeszcze nieokreślone Typ . Funkcja musi zadeklarować typ powrotu z RETURNS klauzula (lub z parametrami OUT lub INOUT). W Twoim przypadku musiałbyś wrócić do anonimowych rekordów, ponieważ liczba, nazwy i typy zwracanych kolumn są różne. Like:

CREATE FUNCTION data_of(integer)
  RETURNS SETOF record AS ...

Jednak nie jest to szczególnie przydatne. W ten sposób będziesz musiał podać listę definicji kolumn przy każdym wywołaniu funkcji. Like:

SELECT * FROM data_of(17)
AS foo (colum_name1 integer
      , colum_name2 text
      , colum_name3 real);

Ale jak byś nawet zrobić to, kiedy nie znasz kolumn wcześniej?
Możesz korzystać z mniej ustrukturyzowanych typów danych dokumentów, takich jak json, jsonb, hstore lub xml:

• Jak przechowywać tabelę danych(lub listę > lub Dictionary) w bazie danych?

Ale na potrzeby tego pytania Załóżmy, że chcesz zwracać pojedyncze, poprawnie wpisane i nazwane kolumny w jak największym stopniu.

Proste rozwiązanie ze stałym zwrotem typ

Kolumna datahora wydaje się być podana, zakładam typ danych timestamp i że zawsze są jeszcze dwie kolumny o różnej nazwie i typie danych.

nazwy porzucimy na rzecz nazw ogólnych w typie return.
typy również porzucimy i oddamy wszystkie do text ponieważ każdy typ danych może zostać oddany do text.

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text) AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';  -- cast each col to text
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = $1
      ORDER  BY datahora'
   USING  _id;

END
$func$ LANGUAGE plpgsql;

• Można wprowadzić zmienne _sensors i _type zamiast tego parametry.

• Uwaga na RETURNS TABLE klauzula.

• Zwróć uwagę na użycie RETURN QUERY EXECUTE. Jest to jeden z bardziej eleganckich sposobów zwracania wierszy z dynamicznego zapytania.

• Używam nazwy dla parametru function, tylko po to, aby USING klauzula RETURN QUERY EXECUTE była mniej myląca. $1 W Sql-string nie odnosi się do parametru function, ale do wartości przekazanej z klauzulą USING. (Oba zdają się być $1 w swoich odpowiednich zakres w tym prostym przykładzie.)

• Zwróć uwagę na przykładową wartość dla _sensors: każda kolumna jest oddana do typu text.

• Ten rodzaj kodu jest bardzo podatny na SQL injection. Używam quote_ident() żeby się przed nim chronić. Połączenie kilku nazw kolumn w zmiennej _sensors zapobiega użyciu quote_ident() (i jest zazwyczaj złym pomysłem!). Upewnij się, że żadne złe rzeczy nie mogą być tam w inny sposób, na przykład poprzez indywidualne bieganie nazwy kolumn zamiast quote_ident(). Na myśl przychodzi parametr VARIADIC...

Prostsze z PostgreSQL 9.1 +

W wersji 9.1 lub nowszej możesz użyć format() aby jeszcze bardziej uprościć:

RETURN QUERY EXECUTE format('
   SELECT datahora, %s  -- identifier passed as unescaped string
   FROM   %I            -- assuming the name is provided by user
   WHERE  id = $1
   ORDER  BY datahora'
  ,_sensors, _type)
USING  _id;

Ponownie, nazwy poszczególnych kolumn mogłyby być odpowiednio unikane i byłyby czystym sposobem.

Zmienna liczba kolumn dzielących ten sam typ

Po aktualizacji Twojego pytania wygląda na to, że Twój typ powrotu ma

• a zmienna liczba kolumn
• ale wszystkie kolumny tego samego typu double precision (alias float8)

Ponieważ musimy zdefiniować typ RETURN funkcji uciekam się w tym przypadku do typu ARRAY, który może pomieścić zmienną liczbę wartości. Dodatkowo zwracam tablicę z nazwami kolumn, więc możesz również przeanalizować nazwy z wyniku:

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[] ) AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';  -- plain list of column names
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT datahora
           , string_to_array($1)  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = $2
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$  LANGUAGE plpgsql;

Różne typy kompletnych tabel

Jeśli naprawdę próbujesz wrócić wszystkie kolumny tabeli (na przykład jedna z tabel na połączonej stronie , następnie użyj tego prostego, bardzo wydajnego rozwiązania z typ polimorficzny:

CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = $1
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$ LANGUAGE plpgsql;

Telefon:

SELECT * FROM data_of(NULL::pcdmet, 17);

Zastąp pcdmet w wywołaniu dowolną inną nazwą tabeli.

• anyelement jest pseudo typem danych, typem polimorficznym, symbolem zastępczym dla dowolnego typu danych innego niż tablica. Wszystkie wystąpienia anyelement w funkcji oceń na ten sam typ podany w czasie wykonywania. Dostarczając do funkcji wartość określonego typu jako argument, domyślnie definiujemy typ zwracany.

• PostgreSQL automatycznie definiuje typ wiersza (złożony typ danych) dla każdej utworzonej tabeli, więc dla każdej tabeli jest dobrze zdefiniowany Typ. Obejmuje to tymczasowe tabele, które są wygodne do użytku ad hoc.

• Każdy typ może być NULL. Przekazujemy więc NULL wartość, rzucamy do tabeli Typ.

• Teraz funkcja zwraca dobrze zdefiniowany typ wiersza i możemy użyć SELECT * FROM data_of(...) do rozłożenia wiersza i uzyskania pojedynczych kolumn.

• pg_typeof(_tbl_type) zwraca nazwę tabeli jako typ identyfikatora obiektu regtype. Po automatycznej konwersji na text, identyfikatory są automatycznie podwójnie cytowane i w razie potrzeby kwalifikowane do schematu. Dlatego SQL injection nie jest możliwe. To może nawet radzić sobie ze schematem-kwalifikowane table-names where quote_ident() would fail .