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()