Início / dba / Perguntas / 208580
Accepted
dakini
Asked: 2018-06-03 07:14:15 +0800 CST

Passando o valor do intervalo do tipo de dados na consulta parametrizada

  • 772

O contexto está se conectando a um banco de dados Postgres de um servidor de descanso.

Para considerar um exemplo hipotético representativo: gostaria de obter uma lista de nomes em que a data de criação da conta é mais antiga/mais recente que um valor arbitrário.

Na consulta de exemplo abaixo, a estrutura da tabela é simples - nameé do tipo texte creation_dateé do tipo timestamp. Então, quando eu faço algo como

server_pg_module:query("select name from new_table where 
current_timestamp - creation_date < '6 days'")

funciona bem. Mas o que eu realmente quero fazer é obter esse valor do 6 daysservidor. Então eu tento algo como

server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]

ele lança um erro. Eu tentei '6 days', "'6 days'"e algumas outras misturas, todos os erros de lançamento. Então, para verificar, adicionei uma nova coluna intervaldo tipo intervale tentei uma consulta como

server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]).

que funciona, mas

server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]).

rompe. Para uma boa medida, além das misturas "'3 days'"mencionadas acima, eu também tentei $2::interval(o que não tenho certeza se é legítimo), mas não funciona.

Assim, acredito que possa ter algo a ver com a expressão de um intervalo em uma consulta de parâmetro ou algo peculiar sobre o módulo que estou usando. Qualquer idéia sobre o que causa o problema e como fazer esse tipo de coisa seria apreciada. Ou pode ser que o problema não seja com pg, mas com o módulo, então eu tenho que resolvê-lo em outro lugar.

Versão do Postgres: 10.x

O módulo que estou usando é pgo (para a linguagem de programação Erlang) https://github.com/SpaceTime-IoT/pgo . A mensagem de erro que recebo (quando passo "2 days"ou "'2 days'"como parâmetro de consulta) se parece com:

{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
                          line => <<"575">>,
                          message => <<"insufficient data left in message">>,
                          routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
                          {unknown,86} => <<"ERROR">>}}}

E quando passo '2 days'como parâmetro, dá um badargerro.

postgresql datatypes

2 respostas

  1. Best Answer

    TLDR: Pule para o capítulo "Consulta superior" abaixo.

    Você não divulgou o módulo com o qual está trabalhando, mas o problema é obviamente de conversão de tipo . Parece que seu parâmetro é passado como valor digitado e presumo textou varchar. Não há conversão de tipo implícita para text-> interval:

    SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'interval'::regtype;

    fonte de elenco | alvo de elenco | contexto de elenco
:--------------------- | :------------- | :----------
hora sem fuso horário | intervalo | eu          
intervalo | intervalo | eu

    db<>fique aqui

    Se minhas suposições estiverem corretas, você deverá ver uma mensagem de erro como:

    ERROR:  operator does not exist: interval < text

    Estou bastante confiante de que seu módulo não divulgado tem maneiras de passar um tipo de dados diferente ou um literal de string não tipado. O Postgres oferece essa funcionalidade.

    Você também afirma:

    eu também tentei$2::interval

    Isso é estranho, porque uma conversão de tipo explícito também deve funcionar.

    Demonstração

    -- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works

    | ?coluna? |
| :------- |
| f |

    --untyped string literal
SELECT current_timestamp - timestamp '2018-05-04 18:40' < '6 days'; -- works

    | ?coluna? |
| :------- |
| f |

    -- text typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < text '6 days'; -- fails!

    ERRO: operador não existe: intervalo < texto

    LINHA 2: ... current_timestamp - timestamp '2018-05-04 18:40' < text '6 ... ^ DICA: Nenhum operador corresponde ao nome e tipo de argumento fornecidos. Talvez seja necessário adicionar conversões de tipo explícitas.

    -- text typed value, with explicit cast
SELECT current_timestamp - timestamp '2018-05-04 18:40' < ('6 days'::text::interval); -- works

    | ?coluna? |
| :------- |
| f |

    O mesmo vale para declarações preparadas:

    PREPARE fooplan_typed_interval(interval) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_interval('6 days');

    | ?coluna? |
| :------- |
| f |

    PREPARE fooplan_untyped AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_untyped('6 days');

    | ?coluna? |
| :------- |
| f |

    PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_text('6 days');  -- only this one fails!

    ERRO: operador não existe: intervalo < texto

    LINHA 3: ...ELECT current_timestamp - timestamp '2018-05-04 18:40' < $1; ^ DICA: Nenhum operador corresponde ao nome e tipo de argumento fornecidos. Talvez seja necessário adicionar conversões de tipo explícitas.

    PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1::interval;

EXECUTE fooplan_typed_text('6 days');

    | ?coluna? |
| :------- |
| f |

    db<>fique aqui

    Consulta superior

    Tudo isso de lado, nenhuma de suas consultas pode usar um índice (não "sargable"). Use algo assim em vez disso!

    server_pg_module:query("select name from new_table
where creation_date > localtimestamp - interval '1 day' * $1", [6])

    Você pode multiplicar um intervalcom integer.
    Ou se você descobrir o problema com a passagem de parâmetros digitados:

    server_pg_module:query("select name from new_table
where creation_date > localtimestamp - $1::interval", ['6 days'])

    Usando localtimestamppara apontar que o "horário atual" depende da configuração do fuso horário atual com o tipo timestamp. Detalhes:

    • 7

  2. Eu descobri uma solução alternativa para atingir o mesmo objetivo.

    A abordagem aproveita o fato de que a diferença de duas datas é um número inteiro; é simples converter um carimbo de data/hora em uma data e valores inteiros podem ser usados ​​em consultas parametrizadas do servidor para o banco de dados.

    server_pg_module:query("select name from new_table where 
current_timestamp::date - timestamp::date < $1", [6])
    • 1

relate perguntas