SQLite Forum

SELECT optimization for constant expression
If you decide to use manual SQL to cons up the statement you can still use CQL to create the result set shape for you and give you a typesafe contract.  All you have to do is make the C code conform to the contract for procedures which is pretty easy.  Then you declare the procedure and implement it yourself.  It will only have to prepare a statement, so you avoid writing all the getters.

This is what that looks like:

-- example (no indices included in the example, you'll want some)
create table person (
  firstname text,
  lastname text,
  birthdate long,
  maidenname text

-- the shape of the arguments and the result are the same as the table
-- this doesn't have to be the case... but it is convenient if it works out
-- you can have different arguments and still return a person.  You
-- might need to do that if you have different nullability for instance.
declare proc match_various(like person) (like person);

-- if match_various is written by hand it only has to prepare the statement
-- and return it in the out argument
-- see the .c file for the contract of match_various
-- if you do it differently it will have arguments that match your spec.

-- extern CQL_WARN_UNUSED cql_code match_various(
--    sqlite3 *_Nonnull _db_,
--    sqlite3_stmt *_Nullable *_Nonnull _result_stmt,
--    cql_string_ref _Nullable firstname_,
--    cql_string_ref _Nullable lastname_,
--    cql_nullable_int64 birthdate_,
--    cql_string_ref _Nullable maidenname_);

-- the following calls match_various and creates a result set
-- the .h file will have the various functions you need to read from it
create proc return_various(like match_various arguments)
  call match_various(from arguments);