Gdybym miał tabelę z 3 kolumnami - powiedzmy A, B i D - i musiałbym wprowadzić nową - powiedzmy C, aby zastąpić obecną pozycję D. Użyłbym następującej metody:
- Wprowadź 2 nowe kolumny jako C i D2.
- Skopiuj zawartość D do D2.
- Usuń D.
- Zmień nazwę D2 na D.
Nowe zamówienie będzie A, B, C i D.
Myślałem, że jest to uzasadniona praktyka, ponieważ (jak dotąd) nie powodowała żadnych problemów.
Jednak dzisiaj natknąłem się na problem, gdy funkcja wykonująca instrukcję w tej samej tabeli zwróciła następujący błąd:
table row type and query-specified row type do not match
I następujący szczegół:
Query provides a value for a dropped column at ordinal position 13
Próbowałem zrestartować PostgreSQL, VACUUM FULLa na końcu usunąć i ponownie utworzyć funkcję, jak to sugerowano tutaj i tutaj, ale te rozwiązania nie działały (poza tym, że próbują rozwiązać sytuację, w której zmieniono tabelę systemową).
Mając luksus pracy z bardzo małą bazą danych, wyeksportowałem ją, usunąłem, a następnie ponownie zaimportowałem, co rozwiązało problem z moją funkcją.
Wiedziałem, że nie należy mieszać się z naturalną kolejnością kolumn , modyfikując tabele systemowe (brudząc sobie ręce pg_attributeitp.), Jak pokazano tutaj:
Czy można zmienić naturalną kolejność kolumn w Postgres?
Sądząc po błędzie rzuconym przez moją funkcję, teraz zdaję sobie sprawę, że zmiana kolejności kolumn za pomocą mojej metody jest również nie-nie. Czy ktoś może świecić światłem, dlaczego to, co robię, jest również złe?
Wersja Postgres to 9.6.0.
Oto funkcja:
CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '
-- First, select the user:
WITH select_user AS
(SELECT
users.id
FROM
users
WHERE
useremail = users.email),
-- Second, update the user (if user exists):
update_user AS
(UPDATE
users
SET
authentication_code = GEN_RANDOM_UUID(),
authentication_date = current_timestamp,
facebook_id = facebookid
WHERE EXISTS (SELECT * FROM select_user)
AND
useremail = users.email
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id),
-- Third, insert the user (if user does not exist):
insert_user AS
(INSERT INTO
users (authentication_code, authentication_date, email, key, name, facebook_id)
SELECT
GEN_RANDOM_UUID(),
current_timestamp,
useremail,
GEN_RANDOM_UUID(),
COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
facebookid
WHERE NOT EXISTS (SELECT * FROM select_user)
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id)
-- Finally, select the authentication code, ID, key and Stripe ID:
SELECT
*
FROM
update_user
UNION ALL
SELECT
*
FROM
insert_user' LANGUAGE "sql" COST 100 ROWS 1
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKERPrzeprowadziłem zmianę nazwy / kolejność na obu kolumnach facebook_idi stripe_id(przed nimi została dodana nowa kolumna, co jest przyczyną zmiany nazwy, ale zapytanie to nie dotyczy).
Posiadanie kolumn w określonej kolejności nie leży w interesie porządku. Jednak powodem zadawania tego pytania nie jest obawa, że prosta zmiana nazwy i usunięcie kolumny może wywołać prawdziwe problemy dla kogoś, kto używa funkcji w trybie produkcyjnym (jak to się stało).