Início / dba / Perguntas / 53044
Accepted
A-K
Asked: 2013-11-09 13:42:20 +0800 CST

Existem outras maneiras de selecionar uma lista dinâmica de colunas?

  • 772

Preciso permitir que meus usuários especifiquem a lista de colunas que desejam selecionar. Até agora, conheço duas maneiras de fazer isso.

1. Usando refcursores

CREATE OR REPLACE FUNCTION selecttestwithcolumnlist(
ticker character varying, 
columnlist character varying)
  RETURNS refcursor AS
$BODY$
DECLARE 
  ref1 refcursor;
BEGIN

OPEN ref1 FOR EXECUTE 
'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'
USING     Ticker;
RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE

Essa função é muito fácil de invocar do meu cliente Ado.Net. Tudo o que preciso fazer é passar os parâmetros. No entanto, se eu quiser testar esta função do pgAdmin, o conjunto de resultados será aberto na tela apenas se eu mantiver minha transação aberta. Isso é inconveniente. Claro, é fácil expor os dados como uma tabela HTML ou uma planilha do Excel, mas isso é um pequeno inconveniente.

2. Usando conjunto de registros

CREATE OR REPLACE FUNCTION SelectPrices(colList VARCHAR)
 RETURNS SETOF record AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || ' FROM prices ORDER BY Ticker, ASOfDate';

END
$func$ LANGUAGE plpgsql;

Infelizmente, isso complica meu código de cliente. Não consigo emitir um SELECT simples assim:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') ;

Devo fornecer explicitamente a estrutura do meu conjunto de resultados:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') 
AS f(price NUMERIC,AsOfdate TIMESTAMP,ticker VARCHAR);

Isso é factível, mas inconveniente.

Existem outras maneiras de retornar listas de colunas dinâmicas?

Edite para proteger contra injeção de SQL, normalmente divido a lista separada por vírgulas e a uno em uma exibição do sistema. Qualquer coisa que não seja um nome de coluna real não é retornado. Eu não mencionei isso originalmente, apenas para manter a pergunta curta.

postgresql dynamic-sql

1 respostas

  1. Best Answer

    Outra forma, semelhante ao que propus para sua pergunta anterior: Retorne um conjunto de tipo bem conhecido. Como sua lista de colunas é dinâmica, crie uma tabela temporária para esse fim. Isso anuncia o tipo para o sistema. Como efeito colateral, você obtém uma tabela temporária para manter os resultados durante a sessão - como precisava em sua última pergunta.

    CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   'SELECT ' || colList || '
    FROM   prices
    WHERE  ...
    ORDER  BY ...';
END
$func$;

    Ligar:

    CREATE TEMP TABLE t (col1 int, col2 date);
SELECT * FROM select_prices(NULL::t, 'col1, col2');

    Ou, para manter os resultados na tabela temporária:

    INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2');

    Se você precisar de várias tabelas nas mesmas sessões, use uma sequência para obter nomes exclusivos. Ver:

    No entanto , este método (assim como os outros dois em sua pergunta) é suscetível à injeção de SQL . Você precisa ter certeza de que não pode ser abusado.

    Novamente, eu tentaria usar esta declaração simples:

    CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;
    • 4

relate perguntas