Jak zachować pierwotną kolejność elementów w nie odkrytej tablicy?

Biorąc pod uwagę ciąg:

„Myślę, że PostgreSQL jest fajny”

Chciałbym operować poszczególnymi słowami znajdującymi się w tym ciągu. Zasadniczo mam osobny element, z którego mogę uzyskać szczegółowe informacje na temat słów i chciałbym dołączyć do nie odkrytej tablicy tego łańcucha w tym słowniku.

Do tej pory mam:

select word, meaning, partofspeech
from unnest(string_to_array('I think that PostgreSQL is nifty',' ')) as word
from table t
join dictionary d
on t.word = d.wordname;

Osiąga to podstawy tego, co miałem nadzieję zrobić, ale nie zachowuje oryginalnej kolejności słów.

Powiązane pytanie:
PostgreSQL unnest () z numerem elementu

WITH ORDINALITY w Postgres 9.4 lub nowszym

Nowa funkcja upraszcza tę klasę problemów. Powyższe zapytanie może teraz być po prostu:

SELECT *
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn);

Lub zastosowane do tabeli:

SELECT *
FROM   tbl t, regexp_split_to_table(t.my_column, ' ') WITH ORDINALITY x(word, rn);

Detale:

• PostgreSQL unnest () z numerem elementu

Informacje o niejawnym LATERALpołączeniu:

• Jaka jest różnica między LATERAL a podzapytaniem w PostgreSQL?

Postgres 9.3 lub starszy - i bardziej ogólne wyjaśnienie

Dla pojedynczego ciągu

Możesz zastosować funkcję okna, row_number()aby zapamiętać kolejność elementów. Jednak w zwykłym przypadku row_number() OVER (ORDER BY col)otrzymujesz liczby zgodnie z kolejnością sortowania , a nie pierwotną pozycję w ciągu.

Możesz po prostu pominąć, ORDER BYaby uzyskać pozycję „jak jest”:

SELECT *, row_number() OVER () AS rn
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') AS x(word);

Wydajność regexp_split_to_table()degradacji z długimi łańcuchami. unnest(string_to_array(...))skaluje się lepiej:

SELECT *, row_number() OVER () AS rn
FROM   unnest(string_to_array('I think Postgres is nifty', ' ')) AS x(word);

Jednak, chociaż normalnie działa to i nigdy nie widziałem, aby łamał się w prostych zapytaniach, Postgres nie zapewnia niczego o kolejności wierszy bez wyraźnego ORDER BY.

Aby zagwarantować liczbę porządkową elementów w oryginalnym łańcuchu, użyj generate_subscript()(poprawione komentarzem @deszo):

SELECT arr[rn] AS word, rn
FROM   (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM   string_to_array('I think Postgres is nifty', ' ') AS x(arr)
   ) y;

Dla tabeli ciągów znaków

Dodaj PARTITION BY iddo OVERklauzuli ...

Tabela demo:

CREATE TEMP TABLE strings(string text);
INSERT INTO strings VALUES
  ('I think Postgres is nifty')
 ,('And it keeps getting better');

Używam ctidjako ad-hoc zamiennika klucza podstawowego . Jeśli masz jedną (lub dowolną unikalną kolumnę ), użyj jej zamiast tego.

SELECT *, row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid, unnest(string_to_array(string, ' ')) AS word
   FROM   strings
   ) x;

To działa bez wyraźnego ID:

SELECT arr[rn] AS word, rn
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM  (
      SELECT string_to_array(string, ' ') AS arr
      FROM   strings
      ) x
   ) y;

SQL Fiddle.

Odpowiedz na pytanie

SELECT z.arr, z.rn, z.word, d.meaning   -- , partofspeech -- ?
FROM  (
   SELECT *, arr[rn] AS word
   FROM  (
      SELECT *, generate_subscripts(arr, 1) AS rn
      FROM  (
         SELECT string_to_array(string, ' ') AS arr
         FROM   strings
         ) x
      ) y
   ) z
JOIN   dictionary d ON d.wordname = z.word
ORDER  BY z.arr, z.rn;