[racket] in-query problems

On Dec 10, 2014, at 12:22 PM, George Neuner <gneuner2 at comcast.net> wrote:

> Hi all,
> 
> I'm using 6.0.1  and having problems trying to use  in-query.
> 
> First:   in-query  is the only function that can use cursors, but, unlike the other row returning functions (query, query-rows, query-maybe-row),  in-query  doesn't appear to understand Postgresql array fields - they are returned as "unreadable".  That's a significant problem for me:  I have a query that may return a large number - perhaps thousands - of rows which contain array fields.  I need to process those fields and insert derived information back into the database before passing on the results of the query to the caller.

That’s surprising, because ‘unreadable should only show up as a component inside of a record value, and there shouldn’t be any difference between how in-query treats them and the way query, query-rows, etc treat them.

Could you be turning entire rows into records (perhaps to deal with the issue below)?

In any case, the underlying problem is (probably) that something has a type that the db library can’t read, and the solution is usually to cast/convert it into something readable, like text.

> I can work around it by getting a list of identifiers for matching rows from a separate query and then iterating the list fetching the rows separately using one of the query functions that works ... but even fetching in groups that will be a lot slower than keep a cursor open on a single query.

One other note: you can also create use cursors through the SQL statements PREPARE, EXECUTE, etc.

> Second ... and this is just annoying ... it seems that, unlike the other row returning functions,  in-query  requires that the result columns be enumerated - i.e. you can't say "select * from ..." to retrieve arbitrary tuples.  That's inconvenient when you really want the whole record and even more so if the columns aren't known statically, which forces the SQL to be  assembled dynamically [never ideal, but necessary sometimes].  I don't have a dynamic situation here, but I have encountered them in the past.  This seems like a needless limitation.

There shouldn’t be any problem with “SELECT * FROM ….” per se. Do you mean that in-query returns a sequence where each step produces multiple values, and to receive those you need the appropriate number of identifiers in your ‘for’ form?

I could add a variant of in-query (maybe ‘in-query-rows’) that for each step produces a vector representing the entire row, rather than one value per field. Would that solve the problem?

Ryan