sql - 在 Postgres 中，我将如何检索列的默认值，最好是在插入语句中内联？

Question

这是我的示例表:

CREATE TABLE IF NOT EXISTS public.cars
(
    id serial PRIMARY KEY,  
    make varchar(32) not null,
    model varchar(32),
    has_automatic_transmission boolean not null default false,
    created_on_date timestamptz not null DEFAULT NOW()
);  

我有一个函数可以让我的数据服务将汽车插入数据库。它看起来像这样:

drop function if exists cars_insert; 
 
create function cars_insert
(
    in make_in text,
    in model_in text,
    in has_automatic_transmission_in boolean,
    in created_on_date_in timestamptz 
)  
returns public.carsas 
$$
    declare result_set public.cars;
    begin   
        insert into cars
        (
            make,
            model,
            has_automatic_transmission,
            created_on_date
        ) 
        values 
        (
            make_in,
            model_in,
            has_automatic_transmission_in,
            created_on_date_in
        ) 
        returning * into result_set;  

        return result_set;
    end;
$$

language 'plpgsql';

这非常有效，直到服务想要插入没有 has_automatic_transmission 或 created_on_date 值的汽车。在这种情况下，他们会为这些参数发送 null，并希望数据库使用默认值。但是数据库出于明显的原因拒绝了该空值(不是空值!)。
我想要做的是让插入例程合并到默认值，但这不起作用。这是我想要的插入逻辑:

insert into cars
        (
            make,
            model,
            has_automatic_transmission,
            created_on_date
        ) 
        values 
        (
            make,
            model,
            COALESCE(has_automatic_transmission_in, DEFAULT),
            COALESCE(created_on_date_in, DEFAULT)
        ) 

我怎样才能有效地实现这一目标？理想情况下，这是一种我可以将内联应用于每一列的方法，这样我们就不需要特别了解哪些列有或没有默认值，但此时我会采取任何措施......
如果可能的话，我想避免使用动态 SQL。

Answer 1

虽然您需要将值传递给函数，并希望动态插入默认值而不是 NULL，但您可以像这样查找它们(但请参阅下面的免责声明!):

CREATE OR REPLACE FUNCTION cars_insert (make_in text
                                      , model_in text
                                      , has_automatic_transmission_in boolean
                                      , created_on_date_in timestamptz)  
  RETURNS public.cars AS
$func$
INSERT INTO cars(make, model, has_automatic_transmission, created_on_date) 
VALUES (make_in
      , model_in
      , COALESCE(has_automatic_transmission_in
               , (SELECT pg_get_expr(d.adbin, d.adrelid)::bool -- default_value
                  FROM   pg_catalog.pg_attribute a
                  JOIN   pg_catalog.pg_attrdef   d ON (d.adrelid, d.adnum) = (a.attrelid, a.attnum) 
                  WHERE  a.attrelid = 'public.cars'::regclass
                  AND    a.attname  = 'has_automatic_transmission'))
      , COALESCE(created_on_date_in
               , (SELECT pg_get_expr(d.adbin, d.adrelid)::timestamptz -- default_value
                  FROM   pg_catalog.pg_attribute a
                  JOIN   pg_catalog.pg_attrdef   d ON (d.adrelid, d.adnum) = (a.attrelid, a.attnum) 
                  WHERE  a.attrelid = 'public.cars'::regclass
                  AND    a.attname  = 'created_on_date'))
       )
RETURNING *;
$func$
LANGUAGE sql;

分贝<> fiddle here
你还得知道专栏 类型 转换从 pg_get_expr() 返回的文本.
我简化为 SQL 函数，因为这里不需要 PL/pgSQL。
看:

Get the default values of table columns in Postgres?

然而， 这仅适用于常量 和类型来自 text 的类型转换被定义为。其他表达式(包括函数)在没有动态 SQL 的情况下不会被评估。 now()在这个例子中只是碰巧工作，因为“现在”(忽略括号)是 timestamptz 的特殊输入字符串计算结果与函数 now() 相同.误导性的巧合。看:

Difference between now() and current_timestamp

为了使其适用于必须评估的表达式，需要动态 SQL - 你排除了它。但是如果允许动态SQL，构建 INSERT的目标列表效率会更高。动态并省略应该获得默认值的列。或者保持目标列表不变并为 DEFAULT 切换 NULL 值关键词。看:

Function to INSERT dynamic list of columns in multiple tables

Test for null in function with varying parameters

Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3