Custom database query

Yes, but you will need to build the query. Vectors are not valid arguments when binding values. The following examples shows two possible options.

// Use same data & query
list = { 2, 1 };
placeholders = strjoin((":"$+ntos(seqa(1,1,rows(list))))', ", ");
query = "SELECT dist, i, j FROM public.dist_table WHERE i IN ("$+placeholders$+") AND j IN ("$+placeholders$+") ORDER BY i,j;";

// Option 1. Immediately execute with GAUSS vector converted to string array as third argument
// I think this is the most efficient option out of any of them.
qid = dbExecQuery(db, query[1], ntos(list));
ret = dbQueryFetchAllM(qid);

// Option 2: Bind values individually. This allows you to bind additional placeholder values.
ig = dbQueryPrepare(qid, query[1]);
for i(1, rows(list), 1);
    dbQueryBindValue(qid, ":"$+ntos(i), ntos(list[i]));
endfor;
ig = dbQueryExecPrepared(qid);
ret = dbQueryFetchAllM(qid);

Also, please note that ODBC placeholders ("?") are not named placeholders.They are intended to be used as purely positional placeholders. Note the following example.

// We want to prepare a query to look like the following:
// "SELECT dist FROM public.dist_table i = 15 OR j = 20"
qid = dbCreateQuery(db_id);
ret = dbQueryPrepare(qid, "SELECT dist FROM public.dist_table i = ? OR j = ?");
dbQueryBindValue(qid, 1, 15); // This binds the first referenced '?' to the value '15'.
dbQueryBindValue(qid, 2, 20); // This binds the second referenced '?' to the value '20'.
ret = dbQueryExecPrepared(qid);

The following is the first example, but using ODBC style placeholders instead. Since they are positional only, they would require duplicating the bind values. The first example does not suffer this issue since we are using named bind values. Note that named bind values are 'Oracle' in name only. They work with many additional DB drivers, so give them a try.

// Use same data & query
list = { 2, 1 };
list_rows = rows(list);
placeholders = strjoin(reshape("?", 1, list_rows), ", ");
query = "SELECT dist, i, j FROM public.dist_table WHERE i IN ("$+placeholders$+") AND j IN ("$+placeholders$+") ORDER BY i,j;";

// Option 1. Immediately execute with GAUSS vector converted to string array as third argument
// Here we do the reshape to account for double the positional placeholders.
qid = dbExecQuery(db, query[1], reshape(ntos(list), list_rows*2, 1));
ret = dbQueryFetchAllM(qid);

// Option 2: Bind values individually. This allows you to bind additional placeholder values.
// This option only sees a minor change
ig = dbQueryPrepare(qid, query[1]);
for i(1, list_rows, 1);
    // We only have to iterate through the bind values once, but we need to make sure we bind both references.
    dbQueryBindValue(qid, i, ntos(list[i]));
    dbQueryBindValue(qid, i+list_rows, ntos(list[i]));
endfor;
ig = dbQueryExecPrepared(qid);
ret = dbQueryFetchAllM(qid);

There are some places in the DB docs that can be improved and is something we're looking into again. We'd like to invite you to send us an e-mail at [email protected] for additional discussion on ways to improve our database API.

Thanks for the detailed response. I was not familiar with value binding in SQL. I will provide some more comments in an email. One correction to the provided solution is that you use named binding (i.e. ':1', ':2'). I think you wanted to do sequential (i.e. '$1', '$2'). For PSQL, they give details in the docs:
PSQL doc

The named bindings in this example should still work in GAUSS with Postgres as the database. The GAUSS code just did some automation to name them but they are not positional bindings. You could put the string ":1" anywhere in the query and it will use the bound value. Most cases you might name it something that is more than relevant, such as ":name", or ":id". In the example it made more sense to just generate them as sequential integers for this specific example.

To summarize, it was intentional. It's possible the dollar style bindings will work but that is not officially supported or tested in the GAUSS API.