未执行的执行人员的结果，例如RETURNING? [英] Results, such as RETURNING, from uncomitted execs?

问题描述

使用libpqxx，是否有可能有一个准备好的语句，其中exec s尚未被commit记录，将结果存储在result中以供以后的准备好的语句使用?

With libpqxx, is it possible for one prepared statement that execs but has not yet been committed to store results in a result for use in later prepared statements?

如果是这样，该怎么办?

If so, how can this be done?

出于可读性考虑，我已将其剥离，但这实际上是我要尝试的操作:

I've stripped it down for readability, but this is essentially what I'm trying to do:

void prepare_write_parent_table(connection_base &c){
    try
    {
        c.prepare("write_parent_table", 
            "INSERT INTO parent_table (column_1) "
            "SELECT $1 "
            "RETURNING id"
        )
        ("character", pqxx::prepare::treat_string);
    }
    catch (const exception &e)
    {
        cerr << e.what() << endl;
    }
}

string write_parent_table(transaction_base &t, string data){
    try
    {
        result parent_table_result = t.prepared("write_parent_table")(data).exec();
        return parent_table_result[0][0].c_str();

    }
    catch (const exception &e)
    {
        cerr << e.what() << endl;
        return "";
    }
}

void prepare_write_child_table(connection_base &c){
    try
    {
        c.prepare("write_child_table", 
            "INSERT INTO child_table (parent_table_id, column_a) "
            "SELECT $1, $2 "
        )
        ("character", pqxx::prepare::treat_string)
        ("character", pqxx::prepare::treat_string);
    }
    catch (const exception &e)
    {
        cerr << e.what() << endl;
    }
}

检查write_parent_table中的return以查看if( == "").如果不是，则继续；否则，继续.否则，我将在此处commit使其失败，或者如果可能的话，最好取消交易；但是，我什至不知道该怎么办.

The return from write_parent_table is checked to see if( == ""). If it isn't, it proceeds; otherwise, I will commit there to let it fail or more preferably cancel the transaction if possible; however, I don't yet know how to do that if it is even possible.

每个parent_table INSERT中总是存在不确定的INSERT数量.

There will always be an uncertain amount of INSERTs into child_table per parent_table INSERT.

推荐答案

使用两个

Simplify the operation by using a single SQL statement for both inserts using a data-modifying CTE. This is much faster than storing intermediary states in the client.

仅当父表中的第一个INSERT成功并且返回id时，子表中的INSERT才会发生:

The INSERT in the child table only happens if the first INSERT in the parent table is successful and returns an id:

void prepare_write_both_tables(connection_base &c){
    try
    {
       c.prepare("write_both_tables", 
          "WITH p AS ("
             "INSERT INTO parent_table (column_1) "
             "SELECT $1 "
             "RETURNING id) "
          "INSERT INTO child_table (parent_table_id, column_a) "
          "SELECT p.id, $2 "
          "FROM   p"
        )
        ("character", pqxx::prepare::treat_string)
        ("character", pqxx::prepare::treat_string);
    }
    catch (const exception &e)
    {
        cerr << e.what() << endl;
    }
}

搜索[postgres]&请参阅数据修改CTE" .
也称为可写CTE". (或可写CTE").

Search for [postgres] & "data-modifying CTE" for more examples.
Also called "writable CTE" (or "writeable CTE").

对于一个单亲父母和 0个对许多个孩子:

For a single parent and 0 to many children:

void prepare_write_both_tables(connection_base &c){
    try
    {
       c.prepare("write_both_tables", 
          "WITH p AS ("
             "INSERT INTO parent_table (column_1) "
             "SELECT $1 "
             "RETURNING id) "
          "INSERT INTO child_table (parent_table_id, column_a) "
          "SELECT p.id, a "
          "FROM   p, unnest($2::text[]) AS a"
        )
        ("character", pqxx::prepare::treat_string)
        ("character", pqxx::prepare::treat_string);
    }
    catch (const exception &e)
    {
        cerr << e.what() << endl;
    }
}

其中第二个参数是文本表示形式的array of text.示例:

Where The second parameter is an array of text in text representation. Example:

{foo,bar,baz}

这将插入与文本数组中的元素一样多的行.对于0个孩子，传递NULL或空数组{}.

This inserts as many rows as there are elements in the text array. For 0 children pass NULL or an empty array {}.

这篇关于未执行的执行人员的结果，例如RETURNING?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！