postgres 常見錯誤之字段關聯不明确

在函數中取寫查詢語句,查詢出來的字段不能在前邊出現,否則會報錯,

drop function if exists func_test;
create or replace function func_test(  )
  returns void as
$$
begin

      drop table if exists tb_date_incoming;
  create temporary table tb_date_incoming as
    select location_dest_id,
           product_id,
           code_color,
           period,
           level,
           order_code,
           production_lot,
           min(write_date) as date_incoming_min,
           max(write_date) as date_incoming_max
    from tb_move_line_property
    where code = 'incoming'
    group by location_dest_id, product_id, code_color, period, level, order_code, production_lot;

  drop table if exists tb_date_outgoing;
  create temporary table tb_date_outgoing as
    select location_dest_id,
           product_id,
           code_color,
           period,
           level,
           order_code,
           production_lot,
           max(write_date) as date_outgoing_max
    from tb_move_line_property
    where code = 'outgoing'
    group by location_dest_id, product_id, code_color, period, level, order_code, production_lot;

end;
$$
language plpgsql
volatile;      

即使是隻從一個表中查詢,上邊的語句在調用時還是會提示location_dest_id 不明确,

解決這個錯誤,就需要在字段前添加标名或者表的别名,

另外,postgres會提示一個錯誤位置行的資訊,之前我一直在pycharm和navicat中執行,是以一直無法找到準确位置,後來發現用pgadmin 才能找到位置資訊,

個人建議,以後寫function 還是老實的将将表名寫在字段前,避免不必要的麻煩.

錯誤提示示例:

ERROR:  錯誤:  字段關聯 "product_id" 是不明确的
CONTEXT:  在SQL語句的第104行的PL/pgSQL函數func_query_stock_quant()