postgresql - type - Podría referirse a una variable PL/pgSQL o una columna de tabla

postgresql select record type

Question

Tengo una función en pgsql

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date)
  RETURNS character varying AS
$$
BEGIN
    RETURN(
        SELECT date_in_bs FROM core.date_conversion
        WHERE date_in_ad = $1
    );
END
$$

  LANGUAGE plpgsql;

Se crea sin errores, pero cuando uso esta función a través del siguiente error:

ERROR:  column reference "date_in_ad" is ambiguous
LINE 3:   WHERE date_in_ad = $1
                ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT (
        SELECT MAX(date_in_bs) FROM core.date_conversion
        WHERE date_in_ad = $1
    )
CONTEXT:  PL/pgSQL function core.date_bs_from_ad(date) line 3 at RETURN
********** Error **********

ERROR: column reference "date_in_ad" is ambiguous
SQL state: 42702
Detail: It could refer to either a PL/pgSQL variable or a table column.
Context: PL/pgSQL function core.date_bs_from_ad(date) line 3 at RETURN

Answer 1

En casos como estos, donde el código es lo suficientemente simple y directo, a veces es útil confiar en uno de estos comandos especiales de plpgsql al comienzo del texto de la función:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

En este caso, se utilizaría de la siguiente manera:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(date_in_ad date)
  RETURNS character varying AS
$$
#variable_conflict use_column
BEGIN
    RETURN(
        SELECT date_in_bs FROM core.date_conversion
        WHERE date_in_ad = $1
    );
END
$$

Esto es especialmente útil para los casos en que el conflicto no es con los parámetros, sino con los nombres de columnas de salida, como este:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(p_date_in_ad date)
  RETURNS TABLE (date_in_bs character varying) AS
$$
BEGIN
    RETURN QUERY
        SELECT date_in_bs FROM core.date_conversion
        WHERE date_in_ad = p_date_in_ad;
END;
$$

La función anterior fallará porque el compilador no puede decidir si date_in_bs es el nombre de la variable de salida o una de las columnas de core.date_conversion . Para problemas como estos, el comando #variable_conflict use_column realmente puede ayudar.

Answer 2

Existe una colisión entre el identificador de SQL y la variable PlpgSQL. No hay limpia, ¿qué quieres? Usted escribió un predicado, eso es VERDADERO siempre.

Buen uso:

• prefijo (generalmente "_") para variables locales
• nombres calificados en SQL incorporado, como table_name.column_name

Así que ambas técnicas (solo una es necesaria)

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(_date_in_ad date)
RETURNS character varying AS $$
BEGIN
  RETURN SELECT dc.date_in_bs
             FROM core.date_conversion dc
            WHERE dc.date_in_ad = _date_in_ad;
END
$$  LANGUAGE plpgsql;

Para estas funciones de una línea es mejor el lenguaje SQL:

CREATE OR REPLACE FUNCTION core.date_bs_from_ad(_date_in_ad date)
RETURNS character varying AS $$
   SELECT dc.date_in_bs
      FROM core.date_conversion dc
     WHERE dc.date_in_ad = $1; 
$$  LANGUAGE sql;