[racket] in-query problems

Hi Ryan,

I'm confused by your response.  What you're saying doesn't match what I 
see.


On 12/10/2014 2:15 PM, Ryan Culpepper wrote:
> On Dec 10, 2014, at 12:22 PM, George Neuner <gneuner2 at comcast.net> wrote:
>
> > 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.

I don't understand.  in-query is returning records (in DBMS parlance)  
... if I say  "select a,b,c from ..." I get a sequence of 3 element 
vectors just as I get a list of 3 element vectors from query or query-rows.

The other query row functions return array fields properly ... I haven't 
tried query-value or query-list with an array field.   Using the exact 
same SQL as for query,  in-query returns the array fields as 
"unreadable".  I don't see why should be a difference.

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

Again I'm not following.  How are you distinguishing "row" from "record"?

> 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.

In my situation, that will make a mess that I will have to reparse into 
a data structure to operate on.

> > 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.

But, per the documentation, all the other query functions run to 
completion before returning - rendering any cursor that may be 
underlying their operation meaningless.   Only  in-quiry  gives access 
to the cursor through an iteration sequence.


> > 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?

As I mentioned above, if I enumerate columns,  in-query  returns a 
sequence of vectors (which is what I expect).  If I don't enumerate,  I 
get an error such as

in-query: query returns wrong number of columns
   statement: "select * from <table>"
   expected: 1
   got: 8

Are you saying that  in-query is supposed to return (rows x columns) 
result values individually?

> Ryan

Thanks,
George

BTW: I'm a '93 Northeastern CCS alum.