Início / dba / Perguntas / 105831
Accepted
Erwin Brandstetter
Asked: 2015-07-03 15:54:25 +0800 CST

Conflito de nomenclatura entre o parâmetro da função e o resultado de JOIN com a cláusula USING

  • 772

Dada esta configuração no Postgres 9.4 atual ( desta questão relacionada ):

CREATE TABLE foo (ts, foo) AS 
VALUES (1, 'A')  -- int, text
     , (7, 'B');

CREATE TABLE bar (ts, bar) AS
VALUES (3, 'C')
     , (5, 'D')
     , (9, 'E');

db<>fiddle aqui (também da pergunta anterior).

Escrevi a SELECTcom a FULL JOINpara atingir o objetivo da questão referenciada. Simplificado:

SELECT ts, f.foo, b.bar
FROM   foo f
FULL   JOIN bar b USING (ts);

Conforme especificações, a forma correta de endereçar a coluna tsé sem qualificação de tabela. Qualquer um dos valores de entrada ( f.tsou b.ts) pode ser NULL. A USINGcláusula cria um caso estranho: introduzir uma coluna de "entrada" que não está realmente presente na entrada. Até agora tão elegante.

Eu coloquei isso em uma função plpgsql. Por conveniência (ou requisitos), quero os mesmos nomes de coluna para o resultado da função de tabela. Portanto, devemos evitar conflitos de nomenclatura entre nomes de colunas e parâmetros de função idênticos. Deve ser evitado escolhendo nomes diferentes, mas aqui estamos:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT COALESCE(f.ts, b.ts), f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

Ênfase em negrito para destacar o problema . Não posso usar tssem qualificação de tabela como antes, porque plpgsql geraria uma exceção (não estritamente necessária, mas provavelmente útil na maioria dos casos):

ERROR:  column reference "ts" is ambiguous
LINE 1: SELECT ts, f.foo, b.bar
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

Sei que posso usar nomes diferentes ou uma subconsulta ou usar outra função. Mas eu me pergunto se há uma maneira de referenciar a coluna. Não consigo usar a qualificação de tabela. Alguém poderia pensar que deveria haver uma maneira.
Existe?

postgresql join

2 respostas

  1. Best Answer

    De acordo com a documentação PL/pgSQL Under the Hood , você pode usar o parâmetro de configuração plpgsql.variable_conflict, antes de criar a função ou no início da definição da função, declarando como deseja que tais conflitos sejam resolvidos.
    As 3 configurações possíveis são error(o padrão) use_variablee use_column:

    CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;
    • 23

  2. O Postgres 14 adiciona a sintaxe SQL para resolver esse problema. Agora você pode anexar uma AScláusula para declarar um novo alias de tabela para as colunas mescladas na USINGlista:

    
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR ts, foo, bar IN
      SELECT merged.ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts) AS merged
   LOOP
      -- so something
      RETURN NEXT;
   END LOOP;
END
$func$;

    O manual:

    USING ( join_column [, ...] ) [ AS join_using_alias ]

    [...]

    Se um join_using_aliasnome for especificado, ele fornecerá um alias de tabela para as colunas de junção. Somente as colunas de junção listadas na USINGcláusula são endereçáveis ​​por esse nome. Ao contrário de um alias regular, isso não oculta os nomes das tabelas unidas do restante da consulta. Além disso, ao contrário de um alias regular, você não pode escrever uma lista de alias de coluna — os nomes de saída das colunas de junção são os mesmos que aparecem na USINGlista.

    • 4

relate perguntas