Thread: Tables acting funny with pgaccess

I'm having a problem with pgaccess that's confounding me.

If I create a table with psql, I have no problems using pgaccess for
queries.  However, if I create a table using pgaccess, things go awry.
Whenever I run a query (using psql or pgaccess) asking for specific
fields from a table created by pgaccess, I get an error similar to the
following:

> Error executing query

> declare mycursor cursor for select t0.lastname from "Present" t0

> PostgreSQL error message: ERROR: func_get_detail: No such attribute or function 'lastname'

> PostgreSQL status: PGRES_FATAL_ERROR

But if it's a table created by psql, it works.  Also, if I run a "select
*" query, it has no problem either.  It's only if I ask for a specific
field from a table created by pgaccess.

I'm using the visual query designer to create the queries.  I'm running
an AMD K6-2 300, 128MB RAM, RedHat Linux 5.2 (without the old Postgres
rpm's), kernel 2.2.1, PostgreSQL 6.4.2, and I have encountered this
error with pgaccess 0.93 and 0.94.  I have also seen the very same error
with pgaccess on a Sun Sparc 20 workstation running RedHat 5.2, kernel
2.0.35.  This also happens when using pgaccess for Windows NT 4.0 to
access the database on the Sun.

Any help would be greatly appreciated.

Thanks!

Ben

Ben Gunter wrote:
>
> > declare mycursor cursor for select t0.lastname from "Present" t0

Send me a small pg_dump of that table with a few records.
I will try it here.

--
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

bgunter@alef.gcsu.edu (Ben Gunter) writes:
> I'm having a problem with pgaccess that's confounding me.
> If I create a table with psql, I have no problems using pgaccess for
> queries.  However, if I create a table using pgaccess, things go awry.

>> PostgreSQL error message: ERROR: func_get_detail: No such attribute or function 'lastname'

> It's only if I ask for a specific
> field from a table created by pgaccess.

At a guess, pgaccess is generating the table with case of field names
preserved?  Ordinarily, table and field names are implicitly downcased,
but not if you put double quotes around 'em.

My bet is that pgaccess is quoting your field names during CREATE TABLE
but not doing so during SELECT ... a bad combination.

            regards, tom lane

Tom Lane wrote:
>
> At a guess, pgaccess is generating the table with case of field names
> preserved?  Ordinarily, table and field names are implicitly downcased,
> but not if you put double quotes around 'em.

A very good guess I could say ! :-)

I have fixed that bug so we have a PgAccess 0.95 version !
The bug was also present in sort field  specification and in filter
field specification.
I have tested with several examples, including field names with mixed
case letters and also having spaces inside field names. It worked.

Please get the latest 0.95 version from http://www.flex.ro/pgaccess or
ftp://ftp.flex.ro/pub/pgaccess/pgaccess-0.95.tar.gz and let me know if
it's working as you expected.

Thanks for your help improving PgAccess,
--
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA