时间戳

首页 > 解决方案 > 如何使用 Postgres 函数中的 if 条件比较两个表值

postgresql - 如何使用 Postgres 函数中的 if 条件比较两个表值

问题描述

create or replace function  trace.get_latest_exception_custom_msg(id varchar)
returns varchar 
language plpgsql
as $$
declare 
msg varchar ;
begin
    
perform t1.message, t1.created_time from table_1 t1 where t1.id  = id order by t1.created_time desc limit 1; 

perform t2.message, t2.created_time from table_2 t2 where t2.id = id order by t2.created_time desc limit 1; 

if date(t1.created_time ) >= date(t2.created_time) then msg= t1.message;

elsif  d date(t1.created_time ) < date(t2.created_time) then msg= t1.message;
else msg =t1.message;

end if;


return msg;
end;

当我调用此函数时，它给出错误 ERROR: missing FROM-clause entry for table "t_1

标签： postgresql

解决方案

您需要将两个 SELECT 查询的结果存储到变量中，以便能够在 IF 语句中使用它们。

您的IF陈述也有点令人困惑，因为所有三个部分都为msg. 我假设您t2.message至少想在一种情况下使用。

create or replace function  trace.get_latest_exception_custom_msg(p_id varchar)
  returns varchar 
language plpgsql
as 
$$
declare 
  t1_msg varchar;
  t1_created date;
  t2_msg varchar;
  t2_created date;
  msg varchar;
begin

  select t1.message, t1.created_time::date
    into t1_msg, t1_created
  from table_1 t1 
  where t1.id = p_id 
  order by t1.created_time desc 
  limit 1; 

  select t2.message, t2.created_time::date
    into t2_msg, t2_created
  from table_2 t2 
  where t2.id = p_id 
  order by t2.created_time desc 
  limit 1; 
    
  if t1_created >= t2_created then 
    msg := t1_msg;
  elsif t1_created < t2_created then 
    msg := t2_msg; --<< ??? 
  else 
    -- this can only happen if one (or both) of the DATEs is NULL. 
    msg := t1_msg; 
  end if;

  return msg;
end; 
$$

推荐阅读