postgresql - 插入后，如何以编程方式将空值转换为列的默认值？

Question

我有一个表，其中我为其某些列提供了默认值。我想创建一个函数，其参数对应于将在将任何空值修改为列的默认值后在表中插入记录的列。我不想以编程方式构造基于哪些参数为空的查询。本质上我想要类似的东西

INSERT into Table (c1, c2, c3, c4)
Values (coalesce(somevar, DEFAULT(c1)), ...)

这可能吗？我见过mysql can do this . postgres 是否提供类似的东西？我使用的是 9.1 版本

更新:这question提供了一些有趣的解决方案，但不幸的是结果总是文本。我想获取默认值作为其真实数据类型，以便我可以使用它来插入它。我试图找到一种解决方案，将默认值从文本转换为它的数据类型(以文本形式提供)，但我找不到方法:

SELECT column_name, column_default, data_type
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'mytable')
AND column_name = 'mycolumn'
ORDER BY ordinal_position;

上面的代码将 column_default 和 data_type 作为文本返回，那么如何将 column_default 转换为 data_type 的值？如果我能做到这一点，那么我的问题就解决了。

Answer 1

如果表定义接受具有所有列默认值的 INSERT，则下面的两步方法可能有效:

CREATE FUNCTION insert_func(c1 typename, c2 typename, ...)
RETURNS VOID AS $$
DECLARE
 r record;
BEGIN
 INSERT into the_table default values  returning *,ctid INTO r;
 UPDATE the_table set
  col1=coalesce(c1,r.col1),
  col2=coalesce(c2,r.col2),
  ...
 WHERE the_table.ctid=r.ctid;
END;
$$ language plpgsql;

诀窍是在插入时将所有默认值放入 r 记录变量中，并随后在 UPDATE 中使用它们来替换任何非默认值。 ctid 是一个伪列，用于指定刚刚插入的行的内部唯一 ID。

警告:如果某些列具有默认空值和非空检查(或暗示不接受默认值的任何检查)，则此方法将不起作用，因为第一个INSERT 会失败。