How to build INSERT query with multiple rows?

[tangyan]

Postgres supports one INSERT statement has multiple values / rows.

How can I build such query in ozo given that I don't know the number of rows in advance?

INSERT INTO test (a, b) VALUES (1, 2) (3, 4) (5, 6);

[neel]

I was going to ask the same question. The number of rows are determined at runtime. So the query string cannot be written at compile time. Is there any existing solution ? I'd prefer some operator<<() overload ro handle this.

[elsid]

With current code it's not possible to create a query with the runtime number of parameters. It's possible to divide such query into chunks with fixed number of parameters. But I assume this is not acceptable in your case. https://github.com/yandex/ozo/pull/268 should resolve this. Look at the example there. Some boilerplate code is required but we may add more advanced support for the insert satement in the future.

[thed636]

Hi all.

Sorry, people, but I have a strong word against a dynamic number of arguments and the solution. The solution exposes the internal interface and this is not suitable for the library, since the interface has been used for internal purposes only and has no concept to model. The library is the concept-based. There is no adequate mechanism in the language to allow have a different type of arguments in the same container without any type-erasure / union involved. Support such a mechanism forces to add new concepts and support them. This costs a lot. But not needed really. The problem is described here has a totally different solution like using an array of composites to insert it into a table using e.g. with a procedure written in plsql. So it should be solved by the combination of using dynamic containers of tuples in client code and the proper query and/or stored procedures.

With best regards, Sergei

PS this sentence is about the original case.

[thed636]

@neel

The number of rows are determined at runtime. So the query string cannot be written at compile time.

This is not the case for that is the library purposed at the moment. Could you please describe your case a little bit more? Because this is not a simple change to the library to support such a case.

[neel]

@neel

The number of rows are determined at runtime. So the query string cannot be written at compile time.

This is not the case for that is the library purposed at the moment. Could you please describe your case a little bit more? Because this is not a simple change to the library to support such a case.

I've not fully gone through the architecture. However it seems like the query is associated with a tuple. So instead of associating with a single tuple it can be associated with a vector of tuples. But for multi insert case just a vector of tuple won't be enough a custom decorator will be necessary I think. It will be type safe as before and the container would be homogenous. At the same time the existing queries may use a transparent decorator that does nothing.

[thed636]

If you want to insert all the rows in a single request, then use a vector of tuples as a query parameter, but of course, you need to "unpack and insert" logic in your query, in this case, maybe it is better to use a stored procedure instead of a sophisticated query. This works. Invoke a single request for a single item of a vector is not the library scope this is on a user side. There is no optimal strategy for inserting rows. It depends on a database and an application model. In the case of using coroutines, it is so simple as an ordinary cycle, in case of callbacks, well you may use an asynchronous operation object or something different. No universal solution here.

[thed636]

I mean for a single request model you may call a stored procedure in your query. Giving this procedure an array of tuples and insert all these tuples inside the procedure. This works fine and type-safe.

[neel]

If you want to insert all the rows in a single request, then use a vector of tuples as a query parameter, but of course, you need to "unpack and insert" logic in your query,

I want to know more about this solution. Is there any example ?

[mvphilip]

See issue 195 for a discussion on this. The summary is you create function that handles the insert. For a trivial case that inserts as is:

CREATE OR REPLACE FUNCTION simple_write_array(_contents TEXT[])
RETURNS VOID AS
$BODY$
BEGIN
    FOR i IN ARRAY_LOWER(_contents, 1) .. ARRAY_UPPER(_contents, 1)
    LOOP
        BEGIN
            EXECUTE _contents[i];
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'simple_write_array error for STR=%s ec=%s es=%s', _contents[i], SQLSTATE, SQLERRM;
        END;
    END LOOP;
END
$BODY$ LANGUAGE plpgsql;

For the insertion:

   std::vector<std::string> m_queries{{"INSERT INTO TABLE foo(id1,id2) VALUES(1,2)"}, {"INSERT INTO TABLE foo(id1,id2) VALUES(3,4)"}};
    ozo::execute(m_connPool[m_ioc],
                 ozo::make_query("select simple_write_array($1::TEXT[])",
                                 std::ref(m_queries)),
                 std::chrono::seconds(5),
                 std::move(callback));

[thed636]

Thanks for the answer!

This is a good point to start. The next step is to change this example for transferring an array of composites and inserting them into a table inside the stored procedure instead of transferring and executing text queries. So this should be a good exercise to learn the mechanism of the stored procedure for people who are not familiar with ones.

[neel]

@mvphilip The example is not type safe. It is executing raw SQL queries.

I would prefer an API like

const auto sql = "INSERT INTO TABLE foo(a,b)"_SQL
sql << tuple(2, 3) << tuple (4, 5) << tuple(6, 7)

[thed636]

@neel looks like there are all the necessary features in the library to implement the desired interface. So you may implement it and make a PR in the project if you wish to share your solution with others. We have no plans or production needs to implement such functionality at the time, but PR that implements such functionality maybe your good contribution to the project.

@tangyan I close the issue since all the necessary examples of how to insert multiply rows has been provided. They are not perfect, but this is a good start point. Please note that the understanding of the stored procedure, array, and composite type is needed to solve the problem. The discussion of a problem like e.g. how to write a stored procedure, in general, is a little bit out of the scope of the library topics. But please feel free to reopen the issue or create a new one if the additional information about the library is needed.

Hope it was helpful.

[tangyan]

This is helpful. Understood. Thank you!