Apologies for the length of this email but my brain has thrown one again...
I am currently working on a wrapper for Postgres using libpq-fe. It's
working but in need of some higher level functions to make result sets
easier to work with and feel more J-like.
Once the query is executed, all results have been stored in RAM by the
library (not my first choice!) and then a value is obtained like so
pqgetvalue result_set;row;col
I've seen the source code for the library and the above translate directly
into an array read;
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
{
if (!check_tuple_field_number(res, tup_num, field_num))
return NULL;
return res->tuples[tup_num][field_num].value;
}
*# mema and management.*
My plan was to write a 'pgquery' verb that takes the connection handle and
a SQL query (details to be settled) and then creates and executes the query
and returns back a table of boxed strings. My first question then is how is
the memory managed? I know from the documentation that memf is the natural
(obverse?) way to release memory but I wanted to know what happens if I
allocate a string with mema then return it and assign it into a boxed
structure. I have an ffi helper I wrote, I call it psz:
psz=: 3 : 'memr (>y),0,_1'
If I assign the return value to a variable:
name =. psz a-mem-address-of-a-C-String
is the pointer aliased or can I now memf the original address and still
have a viable copy of the string later. I ask this because, for the
lifetime of the result set object, the internally allocated memory is
always around until a pqclear is executed. In this particular case I am
hopefully correct in thinking that my returned box list of strings is
stable up until that time.
(In --this case-- I don't need to release anything as the library does it
but I wanted a general asnwer about what memr actually does)
I am more interested in the general case of using 'memr' though; does it
allocate a copy or, as I suspect, is it merely re-typing the memory address
as a string, in the same manner that 'ic' can be used to process integers
and floats. Some insights would be appreciated.
*# looping*
This brings me to how best to extract the data from the internal result
set? My basic flow so far is this:
conn=. pqconnectdb<'dbname=foo'
res=.pqexec conn;'select * from some_table'
nrows=. pqtuples res
ncols=. pqfields res
So I might have 7 rows and 12 columns per row. Obtaining the name of the
columns is done using the pqfname call. I've been playing around and the
basic loop I have is awful!
pgtest =: 3 : 0
nrows=. pqntuples y
ncols=. pqnfields y
NB. get the column names as the first row
for_j. i. ncols do.
c =. pqfname y;j
smoutput psz c
end.
''
)
I know I can do this 'looplessly' but have so far failed. It's going to be
a combination of bonding and maybe a hook or something but as I say, total
quagmire at the moment! The thing I require to be looped is this:
pqfname res;col. NB. where col is i. ncols
My thought process was along the lines of, produce an array of res;i pairs
but that feels ugly, then I thought about and played with trying to just
pass the i. ncols list into a bonded call to ; but somehow got stuck...
So..I am after some nice solutions to how best to iterate ncols to obtain
the initial row of column names and then extract each row of data from the
result set...I know this can be done very succinctly, I continue to work on
it...
Loving the challenge of thinking so differently!
Thanks all,
Sean
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
