Postgres if语句里面的函数插入值
问题描述:
可以使这个函数有效吗?我在Postgres函数的Insert Into查询中有If语句。Postgres if语句里面的函数插入值
你有什么方法?
CREATE FUNCTION proc_api_consumer_audit_insert()
RETURNS TRIGGER AS $api_consumer$
BEGIN
INSERT INTO api_consumer_audit(api_consumer_id, change_type, changed_by, changed_date, business_id_old, business_id_new, name_old, name_new, api_callback_url_old, api_callback_url_new, application_base_url_old, application_base_url_new, authorization_callback_url_old, authorization_callback_url_new, status_old, status_new)
VALUES(NEW.id, 'INSERT', (IF NEW.created_by=null THEN CURRENT_USER ELSE NEW.created_by END IF;), CURRENT_TIMESTAMP, null, NEW.business_id, null, NEW.name, null, NEW.api_callback_url, null, NEW.application_base_url, null, NEW.authorization_callback_url, null, NEW.status);
RETURN NEW;
END;
$api_consumer$ LANGUAGE plpgsql;
答
SQL中没有IF
。在一般人会使用CASE
表达,但在你的情况coalesce()
要短得多:
INSERT INTO api_consumer_audit(...)
VALUES (NEW.id,
'INSERT',
coalesce(new.created_by, current_user),
...);
与CASE表达式同样会是这样的:
INSERT INTO api_consumer_audit(...)
VALUES (NEW.id,
'INSERT',
case
when new.created_by IS NOT NULL then new.created_by
else current_user
end,
...);
当我调用SQL INSERT查询,合并('101')不工作:INSERT INTO api_consumer(business_id,type,status,description,created_by,created_date,modified_by,modified_date,name,origin_country,license_authority_name,registration_date,api_callback_url,application_base_url,authorization_callback_url,address,local_address,license) VALUES ,'PSD2','新','测试触发器进入SERT','kk',CURRENT_TIMESTAMP,'gg',null,'SPRAWDZAM_COS','PL',null,null,null,null,1,1,1);' - 它仍然显示不正确的用户 – xross
案件正在工作!谢谢! – xross
@xross在第一条评论 –