Andy's questions

Se eu tivesse uma tabela com 3 colunas - digamos A, B e D - e tivesse que introduzir uma nova - digamos C para substituir a posição atual de D. Eu usaria o seguinte método:

1. Introduzir 2 novas colunas como C e D2.
2. Copie o conteúdo de D para D2.
3. Excluir D.
4. Renomeie D2 para D.

A nova ordem seria A, B, C e D.

Eu pensei que esta era uma prática legítima, pois (até agora) não produziu problemas.

Porém, hoje me deparei com um problema quando uma função realizando um comando na mesma tabela retornou o seguinte erro:

table row type and query-specified row type do not match

E o seguinte detalhe:

Query provides a value for a dropped column at ordinal position 13

Tentei reiniciar o PostgreSQL, fazer um VACUUM FULLe finalmente excluir e recriar a função conforme sugerido aqui e aqui, mas essas soluções não funcionaram (além do fato de tentarem resolver uma situação em que uma tabela do sistema foi alterada).

Tendo o luxo de trabalhar com um banco de dados muito pequeno, exportei-o, excluí-o e importei-o novamente e isso corrigiu o problema com minha função.

Eu estava ciente do fato de que não se deve mexer com a ordem natural das colunas modificando as tabelas do sistema (sujando as mãos com pg_attribute, etc.) como visto aqui:

É possível alterar a ordem natural das colunas no Postgres?

A julgar pelo erro lançado pela minha função, agora percebo que mudar a ordem das colunas com meu método também é um não-não. Alguém pode esclarecer por que o que estou fazendo também está errado?

A versão do Postgres é 9.6.0.

Aqui está a função:

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 INVOKER

Realizei a renomeação/reordenação em ambas as colunas facebook_ide stripe_id(uma nova coluna foi adicionada antes delas, que é o motivo da renomeação, mas não é tocada por esta consulta).

Ter as colunas em uma determinada ordem é puramente interessante para a ordem. No entanto, o motivo de fazer essa pergunta é a preocupação de que uma simples renomeação e exclusão de uma coluna possa desencadear problemas reais para alguém que usa funções no modo de produção (como aconteceu comigo).

Apenas esperando para confirmar minha observação e obter uma explicação sobre por que isso está acontecendo.

Eu tenho uma função definida como:

CREATE OR REPLACE FUNCTION "public"."__post_users_id_coin" ("coins" integer, "userid" integer) RETURNS TABLE (id integer) AS '
UPDATE
users
SET
coin = coin + coins
WHERE
userid = users.id
RETURNING
users.id' LANGUAGE "sql" COST 100 ROWS 1000
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

Quando eu chamo essa função de uma CTE, ela executa o comando SQL mas não aciona a função, por exemplo:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
1 -- Select 1 but update not performed

Por outro lado, se eu chamar a função de um CTE e depois selecionar o resultado do CTE (ou chamar a função diretamente sem CTE) ele executa o comando SQL e aciona a função, por exemplo:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
*
FROM
test -- Select result and update performed

ou

SELECT * FROM __post_users_id_coin(10,1)

Como não me importo muito com o resultado da função (só preciso dela para realizar a atualização), existe alguma maneira de fazer isso funcionar sem selecionar o resultado do CTE?