Início / dba / Perguntas / 57610
Accepted
oxfist
Asked: 2014-01-28 04:07:20 +0800 CST

Como retornar corretamente um resultado de consulta apenas se não for NULL?

  • 772

Estou escrevendo uma função PL/pgSQL que cria um cursor para uma consulta que preciso verificar se ela retorna algo.

O que estou fazendo é isso:

  1. Execute a consulta
  2. Verifique se ele retorna algo.
  3. Caso contrário, duplique um parâmetro e execute a consulta novamente.
  4. Caso contrário, retorne todas as linhas da consulta.

Achei que verificar se a consulta retornava algo com um cursor era a melhor escolha, pois é uma consulta muito longa (juntando 5 tabelas e muitas colunas) e SELECT ... INTOnão parecia certo, porque eu teria que criar uma TYPEvez que o consulta tem colunas de uma tabela e uma coluna para um cálculo de distância.

O problema é que atualmente estou usando o cursor apenas para verificar se a consulta retornou algo dentro de um loop onde eu abro e fecho. Depois que a consulta retorna algo, saio do loop e retorno a consulta. Eu posso dizer imediatamente que esta é uma solução feia para o que eu preciso. Talvez alguém possa me ajudar com esta questão.

Aqui está um código que mostra o que estou fazendo atualmente.

CREATE FUNCTION store_distance(
    latitude double precision,
    longitude double precision,
    radius double precision,
    tries integer
)
RETURNS TABLE(
    store_id store.id%type,
    store_name store.name%type,
    distance double precision
)
AS
$$
DECLARE
    cur_stores CURSOR FOR
        SELECT
            store.id,
            store.name,
            get_distance(latitude, longitude, store.latitude, store.longitude) distance
        FROM
            store
        WHERE
            store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
            AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
        ORDER BY
            distance ASC;
    count int := 0;
    storerow RECORD;
BEGIN
    LOOP
        IF count = tries THEN
            EXIT;
        END IF;
        OPEN cur_stores;
        FETCH cur_stores INTO storerow;
        IF FOUND THEN
            EXIT;
        END IF;
        radius := radius * 2;
        count := count + 1;
        CLOSE cur_stores;
    END LOOP;
    RETURN QUERY
    SELECT
        store.id,
        store.name,
        get_distance(latitude, longitude, store.latitude, store.longitude) distance
    FROM
        store
    WHERE
        store.latitude BETWEEN (latitude - radius) AND (latitude + radius)
        AND store.longitude BETWEEN (longitude - radius) AND (longitude + radius)
    ORDER BY
        distance ASC;
END;
$$ LANGUAGE PLPGSQL;

Portanto, meu objetivo é fornecer coordenadas, raio e número de tentativas e tentar encontrar lojas nessa caixa de pesquisa. Se nenhuma loja for encontrada, dobro o raio e tento novamente até que algo seja retornado pela consulta ou até que o número de tentativas seja atingido.
A RETURN TABLEparte é basicamente porque quero voltar a distância, então RETURN SETOF storenão adiantou.

postgresql performance

1 respostas

  1. Best Answer

    Eu não acho que você precisa de um cursor aqui. Para encurtar seu código, você pode usar apenas uma visualização. Para melhorar o desempenho, uma visualização materializada deve levá-lo mais longe. O Postgres 9.3 possui recursos integrados, mas você mesmo pode implementá-lo facilmente em versões mais antigas.

    Considere esta forma simplificada:

    CREATE FUNCTION store_distance(_lat    double precision
                              ,_long   double precision
                              ,_radius double precision
                              ,_tries  integer)
RETURNS TABLE(
   store_id   store.id%type
  ,store_name store.name%type
  ,distance   double precision) AS
$func$
DECLARE
   _ct  int   := 0;
   _pos point := point(_lat, _long);
BEGIN
   LOOP
      EXIT WHEN _ct >= _tries
           OR   EXISTS (
            SELECT 1 FROM store s
            WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius));

      _radius := _radius * 2;
      _ct     := _ct + 1;
   END LOOP;

   RETURN QUERY
   SELECT s.id, s.name
         ,get_distance(_lat, _long, s.latitude, s.longitude)
   FROM   store s
   WHERE  point(s.latitude, s.longitude) <@ circle(_pos, _radius);
   ORDER  BY 3;
END
$func$ LANGUAGE plpgsql STRICT;

    Fiz a função STRICTpara desabilitar a entrada NULL, o que poderia resultar em um loop infinito.

    Observe como eu uso círculos com o operador "Contained" em<@ vez de caixas . Pode-se supor que os cálculos sejam um pouco mais caros do que com caixas, mas isso dificilmente importa quando você suporta sua consulta com um índice GiST como:

    CREATE INDEX store_point_gist_idx ON store
USING gist (point(latitude, longitude));

    Você pode considerar armazenar lat / lon pointpara começar e substituir o índice em uma expressão por um mais simples na coluna. Funciona de qualquer maneira, apenas certifique-se de que a consulta corresponda ao índice para que seja usado. Grande diferença para grandes mesas.

    Você pode estar interessado nesta resposta intimamente relacionada ao SO que postei no ano passado - com muito mais explicações e links.

    • 5

relate perguntas