Postgresql，选择一个“虚假”的行。

Question

在Postgres 8.4或更高版本中，获取由默认值填充的数据行的最有效方式是什么，而不需要实际创建该行。例如，作为一个事务（伪代码）：

create table "mytable"
(
  id serial PRIMARY KEY NOT NULL,
  parent_id integer NOT NULL DEFAULT 1,
  random_id integer NOT NULL DEFAULT random(),
)

begin transaction
  fake_row = insert into mytable (id) values (0) returning *;
  delete from mytable where id=0;
  return fake_row;
end transaction

基本上，我期望查询一行数据，其中parent_id为1，random_id是一个随机数（或其他函数返回值），但我不希望这条记录在表中持久存在或影响主键序列serial_id_seq。

我的选择似乎是使用像上面那样的事务或创建视图，这些视图是带有虚假行的表的副本，但我不知道每种方法的利弊，也不知道是否存在更好的方法。

我正在寻找一个答案，假设没有任何列的数据类型或默认值的先前知识，除了id之外。只有表名是已知的，并且表中不应存在id为0的记录。

过去，我将虚假记录0创建为永久记录，但我认为这条记录是一种污染（因为我通常必须将其从未来的查询中过滤掉）。

- SpliFF

---

我想要一行数据，它恰好是我插入时得到的数据（让默认值在数据库中填充），并且可以立即选择，但不需要使用事务的开销/复杂性和/或副作用（除非事务确实是最好的方法）。 - SpliFF

查询序列的操作是原子性的，无论您是否回滚事务，它都会自增。这对您可能不是问题，但值得一提。 - bma

是的，顺序可能会成为问题。我宁愿不在序列中创建很多空隙。这并不是至关重要的，但它确实使调试更加容易（知道可能存在并已被删除的记录与那些从未真正创建过的记录之间的区别）。 - SpliFF

Answer 1

您可以使用以下代码将表定义和默认值复制到临时表中：

CREATE TEMP TABLE table_name_rt (LIKE table_name INCLUDING DEFAULTS);

使用这个临时表生成虚拟行。这个表将在会话（或事务）结束时被删除，只能在当前会话中看到。

- Ihor Romanchenko

---

谢谢。你的代码在LIKE和INCLUDING子句周围缺少必需的括号，所以我已经更新了你的答案。 - SpliFF

Answer 2

您可以查询目录并构建动态查询。

假设我们有以下表格：

create table test10(
      id serial primary key,
      first_name varchar( 100 ),
      last_name  varchar( 100 ) default 'Tom',
      age int not null default 38,
      salary float  default 100.22
);

当您运行以下查询时：

SELECT string_agg( txt, ' ' order by id ) 
FROM (
select 1 id, 'SELECT ' txt
union all
select 2, -9999 || ' as id '
union all
select 3, ', '  
       || coalesce( column_default,  'null'||'::'||c.data_type ) 
       || ' as ' || c.column_name
from information_schema.columns c
where table_schema = 'public'
    and table_name = 'test10'
    and ordinal_position > 1
) xx
    ;

您将会得到以下字符串结果：

"SELECT  -9999 as id  , null::character varying as first_name , 
'Tom'::character varying as last_name , 38 as age , 100.22 as salary"

然后执行此查询，您将得到“幻象行”。

我们可以构建一个函数，构建并执行查询，并将我们的行作为结果返回：

CREATE OR REPLACE FUNCTION get_phantom_rec (p_i test10.id%type ) 
returns test10 as $$
DECLARE 
    v_sql text;
    myrow test10%rowtype;
begin
   SELECT string_agg( txt, ' ' order by id ) 
   INTO v_sql
   FROM (
    select 1 id, 'SELECT ' txt
    union all
    select 2, p_i || ' as id '
    union all
    select 3, ', '  
           || coalesce( column_default,  'null'||'::'||c.data_type ) 
           || ' as ' || c.column_name
    from information_schema.columns c
    where table_schema = 'public'
        and table_name = 'test10'
        and ordinal_position > 1
    ) xx
    ;
    EXECUTE v_sql INTO myrow;
    RETURN  myrow;
END$$ LANGUAGE plpgsql ;

然后，这个简单的查询将给你想要的结果：

select * from get_phantom_rec ( -9999 );

  id   | first_name | last_name | age | salary
-------+------------+-----------+-----+--------
 -9999 |            | Tom       |  38 | 100.22

- krokodilko

---

哇，你花了多长时间做这个？仅凭努力，这个答案就值得一份赏金。虽然我可能不会选择这个解决方案，因为Igor发布的临时表方法看起来更容易，但我怀疑你的答案在性能是问题的情况下可能更快（在我的情况下不是）。无论如何，你的答案都很有教育意义。 - SpliFF

Answer 3

我会将伪造的值直接选择为文字：

select 1 id, 1 parent_id, 1 user_id

返回的行（虚拟上）与真实行几乎无法区分。

---

要从目录中获取值：

select
  0 as id, -- special case for serial type, just return 0
  (select column_default::int -- Cast to int, because we know the column is int
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'mytable'
   and column_name = 'parent_id') as parent_id,
  (select column_default::int -- Cast to int, because we know the column is int
   from INFORMATION_SCHEMA.COLUMNS
   where table_name = 'mytable'
   and column_name = 'user_id') as user_id;

请注意，您必须了解列及其类型，但这很合理。如果更改表模式（除默认值外），则需要微调查询语句。

请参见上面的SQLFiddle。

- Bohemian

---

我不想知道somecol的类型或默认值，因为这就是我想要检索的内容。编辑：我想获取表格的所有列，而不需要提前了解表格结构。 - SpliFF

是的，我总是希望默认值完全按照“DEFAULT”子句中定义的返回。 - SpliFF

模式目录的问题在于您可以获得默认值的字面字符串定义（例如，random()），但无法获得计算出的默认值（例如，2348756）（请参见更新的fiddle）。 - SpliFF

上面的代码在默认值为函数时无法正常工作。我需要DEFAULT函数运行。 - SpliFF

我甚至不会考虑它。在我看来，这会使数据库变得混乱。留给应用程序代码处理。编码和使用更容易。 - Bohemian