Re: Access to Postgres query problem

In general, you should see much better performance if you change the query type
to a Pass-Through query, as Martin suggested. If you do that, then not only with
Postgres do the SUM() for you, but it will also do all the joins, where
conditions, and everything else. If you run a query in Access through the normal
query builder without passing it through, then every table mentioned in the
query has to be downloaded in its entirety to the client machine so that Access
can apply the where clauses and so forth. Unless all your tables are very small
to begin with, this will eat up a lot of your network bandwidth, especially if
multiplied by lots of Access clients using the same database this way. You
really want to avoid this by using passthrough queries whenever possible.

I think you can overload the multiply operator in postgresql to handle the
multiplication the way you want; check the docs and mailing list archives for
operator overloading.

HTH,

Wes Sheldahl

"Booth, Robert" <Robert_Booth%intuit(dot)com(at)interlock(dot)lexmark(dot)com> on 03/19/2002
05:59:23 PM

To: pgsql-general%postgresql(dot)org(at)interlock(dot)lexmark(dot)com
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] Access to Postgres query problem

I need to run a query in an Access client to my Postgres backend.

This is the query:

<snip>

Now to my question. When I do this from within Access it wants to use it's
own SUM and so it complains about the cast(). How can I either force Access
into sending the entire query to Postgres and just waiting for the
resultset, or force Access into performing the multiplication also?

Or my other thought is that I need to build my own operator/function to
handle the multiplying of double precision and numeric data types. If this
is what I should do how should I go about it?

Thanks,
Rob

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org