Обсуждение: Problems with column names in dot-notation

Поиск
Список
Период
Сортировка

Problems with column names in dot-notation

От
Carsten Heidmann
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,
I'm using PostgreSQL 7.2.1 on Solaris 8 (SunOS 5.8). I'm trying to get a
String from my db, using a very simple select command. Executing the
command in psql or even via phpPgAdmin works fine, but when I try to
retrieve the same information via JDBC I get the following error:
"SQL-Error: The column name sometable.somefield not found."
Executing the same command with a field name without the table name
("somefield") works, when the field name is not ambiguous.

If anyone has a solution for this, I would really appreciate it.

My locale and the db-encoding is set to en_US.ISO8859-15 and the JVM is
started with "-Dfile.encoding=ISO8859-15".

Regards, Carsten

-----BEGIN PGP SIGNATURE-----
Version: 6.5.8ckt

iQA/AwUBPVpDN7jxzYr/SJ6uEQIyegCePfDJeUjBFAltI0rLqthugjijo1oAoKn0
HdcIvr5ouzq9/Ym5Uyn2BtT9
=wCmq
-----END PGP SIGNATURE-----
-----------------------------------------------------------
Dipl. Geogr. Carsten Heidmann
Tel.: 040.81908.345
Fax: 040.81908.373
heidmann@hamburg.baw.de
http://nokis.baw.de/

Bundesanstalt fuer Wasserbau
- Aussenstelle Kueste -
Wedeler Landstrasse 157
DE - 22559 Hamburg
-----------------------------------------------------------

Re: Problems with column names in dot-notation

От
Dave Cramer
Дата:
Can you get access to the server logs? I do these types of queries all
the time?

Dave
On Wed, 2002-08-14 at 08:47, Carsten Heidmann wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
> I'm using PostgreSQL 7.2.1 on Solaris 8 (SunOS 5.8). I'm trying to get a
> String from my db, using a very simple select command. Executing the
> command in psql or even via phpPgAdmin works fine, but when I try to
> retrieve the same information via JDBC I get the following error:
> "SQL-Error: The column name sometable.somefield not found."
> Executing the same command with a field name without the table name
> ("somefield") works, when the field name is not ambiguous.
>
> If anyone has a solution for this, I would really appreciate it.
>
> My locale and the db-encoding is set to en_US.ISO8859-15 and the JVM is
> started with "-Dfile.encoding=ISO8859-15".
>
> Regards, Carsten
>
> -----BEGIN PGP SIGNATURE-----
> Version: 6.5.8ckt
>
> iQA/AwUBPVpDN7jxzYr/SJ6uEQIyegCePfDJeUjBFAltI0rLqthugjijo1oAoKn0
> HdcIvr5ouzq9/Ym5Uyn2BtT9
> =wCmq
> -----END PGP SIGNATURE-----
> -----------------------------------------------------------
> Dipl. Geogr. Carsten Heidmann
> Tel.: 040.81908.345
> Fax: 040.81908.373
> heidmann@hamburg.baw.de
> http://nokis.baw.de/
>
> Bundesanstalt fuer Wasserbau
> - Aussenstelle Kueste -
> Wedeler Landstrasse 157
> DE - 22559 Hamburg
> -----------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Re: Problems with column names in dot-notation

От
Carsten Heidmann
Дата:
At 14.08.2002 08:55 -0400, Dave Cramer wrote:
>Can you get access to the server logs? I do these types of queries all
>the time?

The tomcat log gives me only the already mentioned error ("SQL-Error: The
column name sometable.somefield not found.")

Here are two snippets from the postgresql log:

/usr/local/pgsql/bin/postmaster: PostmasterMain: initial environ dump:
-----------------------------------------
         ANT_HOME=/usr/local/ant
         APACHE_HOME=/usr/local/apache
         CATALINA_HOME=/usr/local/tomcat4
         CC=gcc

CLASSPATH=:/usr/local/pgsql/share/java/postgresql.jar:/opt/classes:/opt/classes/postgis.jar:/opt/classes/DDConnectionBroker.jar:/usr/local/tomcat4/common/lib/servlet.jar:/usr/local/tomcat4/common/lib/xerces.jar:/data/www1/WEB-INF/classes:.
         HOME=/
         HZ=100
         JAVA_HOME=/usr/java
         LANG=de_DE
         LC_ALL=en_US.ISO8859-15@euro
         LOGNAME=root
         MAIL=/var/mail/root

PATH=/usr/local/pgsql/bin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/ant/bin:/usr/ccs/bin:/usr/local/apache/bin:/export/home/ak3cvogt/bin
         PGDATA=/data/db/db1
         PGHOME=/usr/local/pgsql
         PGLIB=/usr/local/pgsql/lib
         SHELL=/sbin/sh
         TERM=vt100
         TOMCAT_HOME=/usr/local/tomcat3
         TZ=MET
         _INIT_NET_STRATEGY=none
         _INIT_PREV_LEVEL=S
         _INIT_RUN_LEVEL=3
         _INIT_RUN_NPREV=0
         _INIT_UTS_ISA=sparc
         _INIT_UTS_MACHINE=sun4u
         _INIT_UTS_NODENAME=nokis
         _INIT_UTS_PLATFORM=SUNW,Sun-Blade-100
         _INIT_UTS_RELEASE=5.8
         _INIT_UTS_SYSNAME=SunOS
         _INIT_UTS_VERSION=Generic_108528-15
-----------------------------------------
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
invoking IpcMemoryCreate(size=917504)
FindExec: found "/usr/local/pgsql/bin/postmaster" using argv[0]
DEBUG:  database system was shut down at 2002-08-14 15:24:44 MEST
DEBUG:  checkpoint record is at 0/309868
DEBUG:  redo record is at 0/309868; undo record is at 0/0; shutdown TRUE
DEBUG:  next transaction id: 11987; next oid: 16992
DEBUG:  database system is ready
DEBUG:  proc_exit(0)
DEBUG:  shmem_exit(0)
DEBUG:  exit(0)
DEBUG:  reaping dead processes
DEBUG:  BackendStartup: forked pid=5084 socket=8

[--snip--]

DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  BackendStartup: forked pid=5090 socket=8
/usr/local/pgsql/bin/postmaster child[5090]: starting with (postgres -d4
-v131072 -p nokis_contacts )
DEBUG:  InitPostgres
DEBUG:  StartTransactionCommand
DEBUG:  query: set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
getdatabaseencoding() end;
DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 :into
<> :isPortal false :isBinary false :isTemp false :hasAggs false
:hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <>
:groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  ProcessUtility: set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
getdatabaseencoding() end;
DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 :into
<> :isPortal false :isBinary false :isTemp false :hasAggs false
:hasSubLinks false :rtable <> :jointree { FROMEXPR :fromlist <> :quals <>}
:rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
25 :restypmod -1 :resname version :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { EXPR :typeOid 25  :opType func :oper { FUNC
:funcid 89 :functype 25 } :args <>}} { TARGETENTRY :resdom { RESDOM :resno
2 :restype 19 :restypmod -1 :resname getdatabaseencoding :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CASE :casetype 19
:arg <> :args ({ WHEN { EXPR :typeOid 16  :opType op :oper { OPER :opno 93
:opid 0 :opresulttype 16 } :args ({ EXPR :typeOid 19  :opType func :oper {
FUNC :funcid 1597 :functype 19 } :args ({ CONST :consttype 23 :constlen 4
:constbyval true :constisnull false :constvalue  4 [ 0 0 0 1 ] })} { CONST
:consttype 19 :constlen 32 :constbyval false :constisnull false
:constvalue  32 [ 83 81 76 95 65 83 67 73 73 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 ] })} :then { CONST :consttype 19 :constlen 32 :constbyval
false :constisnull false :constvalue  32 [ 85 78 75 78 79 87 78 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ] }}) :defresult { EXPR :typeOid
19  :opType func :oper { FUNC :funcid 1039 :functype 19 } :args <>}}})
:groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  plan: { RESULT :startup_cost 0.00 :total_cost 0.01 :rows 1 :width 0
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25
:restypmod -1 :resname version :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { EXPR :typeOid 25  :opType func :oper { FUNC
:funcid 89 :functype 25 } :args <>}} { TARGETENTRY :resdom { RESDOM :resno
2 :restype 19 :restypmod -1 :resname getdatabaseencoding :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CASE :casetype 19
:arg <> :args ({ WHEN { EXPR :typeOid 16  :opType op :oper { OPER :opno 93
:opid 62 :opresulttype 16 } :args ({ EXPR :typeOid 19  :opType func :oper {
FUNC :funcid 1597 :functype 19 } :args ({ CONST :consttype 23 :constlen 4
:constbyval true :constisnull false :constvalue  4 [ 0 0 0 1 ] })} { CONST
:consttype 19 :constlen 32 :constbyval false :constisnull false
:constvalue  32 [ 83 81 76 95 65 83 67 73 73 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 ] })} :then { CONST :consttype 19 :constlen 32 :constbyval
false :constisnull false :constvalue  32 [ 85 78 75 78 79 87 78 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ] }}) :defresult { EXPR :typeOid
19  :opType func :oper { FUNC :funcid 1039 :functype 19 } :args <>}}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :resconstantqual <>}
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
DEBUG:  query: select contact.url from person, department, company,
contact, country where person.person_id = 4 and person.department_id =
department.department_id and department.company_id = company.company_id and
contact.country_id = country.country_id and person.contact_id =
contact.contact_id
DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 :into
<> :isPortal false :isBinary false :isTemp false :hasAggs false
:hasSubLinks false :rtable ({ RTE :relname person :relid 16965  :subquery
<> :alias <> :eref { ATTR :relname person :attrs (
"person_id"   "is_nokis_contact"   "first_name"   "last_name"   "title"
"position"   "contact_id"   "phone"   "private_phone"   "cellphone"   "fax"
   "department_id"   "email" )} :inh true :inFromCl true :checkForRead true
:checkForWrite false :checkAsUser 0} { RTE :relname department :relid
16945  :subquery <> :alias <> :eref { ATTR :relname department :attrs (
"department_id"   "name_de"   "contact_id"   "name_en"   "company_id" )}
:inh true :inFromCl true :checkForRead true :checkForWrite false
:checkAsUser 0} { RTE :relname company :relid 16900  :subquery <> :alias <>
:eref { ATTR :relname company :attrs (
"company_id"   "name_1_de"   "name_2_de"   "acronym_de"   "contact_id"
"name_1_en"   "name_2_en"   "acronym_en" )} :inh true :inFromCl true
:checkForRead true :checkForWrite false :checkAsUser 0} { RTE :relname
contact :relid 16918  :subquery <> :alias <> :eref { ATTR :relname contact
:attrs (
"contact_id"   "street_1"   "street_2"   "plz_street"   "city_street"
"postbox"   "plz_postbox"   "city_postbox"   "country_id"   "url"   "email"
   "phone"   "fax"   "cellphone" )} :inh true :inFromCl true :checkForRead
true :checkForWrite false :checkAsUser 0} { RTE :relname country :relid
16938  :subquery <> :alias <> :eref { ATTR :relname country :attrs (
"country_id"   "iso_acronym"   "name_de"   "name_en" )} :inh true :inFromCl
true :checkForRead true :checkForWrite false :checkAsUser 0}) :jointree {
FROMEXPR :fromlist ({ RANGETBLREF 1 } { RANGETBLREF 2 } { RANGETBLREF 3 } {
RANGETBLREF 4 } { RANGETBLREF 5 }) :quals { EXPR :typeOid 16  :opType and
:oper <> :args ({ EXPR :typeOid 16  :opType and :oper <> :args ({ EXPR
:typeOid 16  :opType and :oper <> :args ({ EXPR :typeOid 16  :opType and
:oper <> :args ({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid
0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 0 0 0 4
] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 0
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 12 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12} { VAR :varno 2
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno 1})})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 0 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 5 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5} { VAR :varno 3
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3
:varoattno 1})})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 0 :opresulttype 16 } :args ({ VAR :varno 4 :varattno 9 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9} { VAR :varno 5
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5
:varoattno 1})})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7} { VAR :varno 4
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4
:varoattno 1})})}} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 1043 :restypmod 84 :resname url :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 4 :varattno 10
:vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}})
:groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
DEBUG:  plan: { NESTLOOP :startup_cost 0.00 :total_cost 24.17 :rows 1
:width 90 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
1043 :restypmod 84 :resname url :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65001 :varattno 3 :vartype 1043
:vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}}) :qpqual <>
:lefttree { NESTLOOP :startup_cost 0.00 :total_cost 19.33 :rows 1 :width 86
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 2
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 84 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 3 :vartype 1043 :vartypmod 84  :varlevelsup 0
:varnoold 4 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 4
:restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 4
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 5 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 4 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype
23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65001 :varattno 6 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 7 :restype 23 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001
:varattno 7 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65000 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 3 :varoattno 1}}) :qpqual <> :lefttree {
NESTLOOP :startup_cost 0.00 :total_cost 14.49 :rows 1 :width 82
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 2
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 84 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 3 :vartype 1043 :vartypmod 84  :varlevelsup 0
:varnoold 4 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 4
:restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 4
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 5 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 4 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype
23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 7 :restype 23 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000
:varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno 5}}) :qpqual <> :lefttree { NESTLOOP :startup_cost 0.00
:total_cost 9.66 :rows 1 :width 74 :qptargetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 1
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype
1043 :restypmod 84 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 1043
:vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}} { TARGETENTRY
:resdom { RESDOM :resno 4 :restype 23 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000
:varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4
:varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65000 :varattno 3 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 4 :varoattno 1}}) :qpqual <> :lefttree {
INDEXSCAN :startup_cost 0.00 :total_cost 4.82 :rows 1 :width 8
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 1 :varattno 12 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 7
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :scanrelid 1 :indxid ( 16970) :indxqual (({ EXPR :typeOid
16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue  4 [ 0 0 0 4 ] })})) :indxqualorig (({
EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23
:constlen 4 :constbyval true :constisnull false :constvalue  4 [ 0 0 0 4 ]
})})) :indxorderdir 1 } :righttree { INDEXSCAN :startup_cost 0.00
:total_cost 4.82 :rows 1 :width 66 :qptargetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 1043 :restypmod 84 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 4 :varattno 10
:vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 4 :varattno 9 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4
:varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 4 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 4 :varoattno 1}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 4
:indxid ( 16920) :indxqual (({ EXPR :typeOid 16  :opType op :oper { OPER
:opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 4 :varattno 1
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 1} { VAR
:varno 65001 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 7})})) :indxqualorig (({ EXPR :typeOid 16  :opType
op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno
65001 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 7} { VAR :varno 4 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 4 :varoattno 1})})) :indxorderdir 0 } :extprm
() :locprm () :initplan <> :nprm 0  :jointype 0 :joinqual <>} :righttree {
INDEXSCAN :startup_cost 0.00 :total_cost 4.82 :rows 1 :width 8
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 2 :varattno 1 :vartype 23 :vartypmod
-1  :varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 5
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :scanrelid 2 :indxid ( 16947) :indxqual (({ EXPR :typeOid
16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 2 :varoattno 1} { VAR :varno 65001 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12})})) :indxqualorig
(({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 65001 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12} { VAR :varno 2
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
:nprm 0  :jointype 0 :joinqual <>} :righttree { INDEXSCAN :startup_cost
0.00 :total_cost 4.82 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom
{ RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 1}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :scanrelid 3 :indxid ( 16903) :indxqual (({ EXPR :typeOid
16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 3 :varoattno 1} { VAR :varno 65001 :varattno 7 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5})})) :indxqualorig
(({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 65001 :varattno 7 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5} { VAR :varno 3
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3
:varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
:nprm 0  :jointype 0 :joinqual <>} :righttree { INDEXSCAN :startup_cost
0.00 :total_cost 4.82 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom
{ RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 5 :varattno 1
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 :varoattno 1}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :scanrelid 5 :indxid ( 16940) :indxqual (({ EXPR :typeOid
16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
VAR :varno 5 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 5 :varoattno 1} { VAR :varno 65001 :varattno 4 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9})})) :indxqualorig
(({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 65001 :varattno 4 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9} { VAR :varno 5
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5
:varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
:nprm 0  :jointype 0 :joinqual <>}
-----------------------------------------------------------
Dipl. Geogr. Carsten Heidmann
Tel.: 040.81908.345
Fax: 040.81908.373
heidmann@hamburg.baw.de
http://nokis.baw.de/

Bundesanstalt fuer Wasserbau
- Aussenstelle Kueste -
Wedeler Landstrasse 157
DE - 22559 Hamburg
-----------------------------------------------------------

Re: Problems with column names in dot-notation

От
Dave Cramer
Дата:
Well it doesn't look like postgres is having any trouble with it. There
would be information in the log saying the column can't be found. Is it
possible that whatever is handling the resultset after that can't find
it?

You are aware that postgres will return postgres will return contact.url
into a column named url?

Dave
On Wed, 2002-08-14 at 09:38, Carsten Heidmann wrote:
> At 14.08.2002 08:55 -0400, Dave Cramer wrote:
> >Can you get access to the server logs? I do these types of queries all
> >the time?
>
> The tomcat log gives me only the already mentioned error ("SQL-Error: The
> column name sometable.somefield not found.")
>
> Here are two snippets from the postgresql log:
>
> /usr/local/pgsql/bin/postmaster: PostmasterMain: initial environ dump:
> -----------------------------------------
>          ANT_HOME=/usr/local/ant
>          APACHE_HOME=/usr/local/apache
>          CATALINA_HOME=/usr/local/tomcat4
>          CC=gcc
>
CLASSPATH=:/usr/local/pgsql/share/java/postgresql.jar:/opt/classes:/opt/classes/postgis.jar:/opt/classes/DDConnectionBroker.jar:/usr/local/tomcat4/common/lib/servlet.jar:/usr/local/tomcat4/common/lib/xerces.jar:/data/www1/WEB-INF/classes:.
>          HOME=/
>          HZ=100
>          JAVA_HOME=/usr/java
>          LANG=de_DE
>          LC_ALL=en_US.ISO8859-15@euro
>          LOGNAME=root
>          MAIL=/var/mail/root
>
PATH=/usr/local/pgsql/bin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/ant/bin:/usr/ccs/bin:/usr/local/apache/bin:/export/home/ak3cvogt/bin
>          PGDATA=/data/db/db1
>          PGHOME=/usr/local/pgsql
>          PGLIB=/usr/local/pgsql/lib
>          SHELL=/sbin/sh
>          TERM=vt100
>          TOMCAT_HOME=/usr/local/tomcat3
>          TZ=MET
>          _INIT_NET_STRATEGY=none
>          _INIT_PREV_LEVEL=S
>          _INIT_RUN_LEVEL=3
>          _INIT_RUN_NPREV=0
>          _INIT_UTS_ISA=sparc
>          _INIT_UTS_MACHINE=sun4u
>          _INIT_UTS_NODENAME=nokis
>          _INIT_UTS_PLATFORM=SUNW,Sun-Blade-100
>          _INIT_UTS_RELEASE=5.8
>          _INIT_UTS_SYSNAME=SunOS
>          _INIT_UTS_VERSION=Generic_108528-15
> -----------------------------------------
> FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
> invoking IpcMemoryCreate(size=917504)
> FindExec: found "/usr/local/pgsql/bin/postmaster" using argv[0]
> DEBUG:  database system was shut down at 2002-08-14 15:24:44 MEST
> DEBUG:  checkpoint record is at 0/309868
> DEBUG:  redo record is at 0/309868; undo record is at 0/0; shutdown TRUE
> DEBUG:  next transaction id: 11987; next oid: 16992
> DEBUG:  database system is ready
> DEBUG:  proc_exit(0)
> DEBUG:  shmem_exit(0)
> DEBUG:  exit(0)
> DEBUG:  reaping dead processes
> DEBUG:  BackendStartup: forked pid=5084 socket=8
>
> [--snip--]
>
> DEBUG:  ProcessQuery
> DEBUG:  CommitTransactionCommand
> DEBUG:  BackendStartup: forked pid=5090 socket=8
> /usr/local/pgsql/bin/postmaster child[5090]: starting with (postgres -d4
> -v131072 -p nokis_contacts )
> DEBUG:  InitPostgres
> DEBUG:  StartTransactionCommand
> DEBUG:  query: set datestyle to 'ISO'; select version(), case when
> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
> getdatabaseencoding() end;
> DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 :into
> <>:isPortal false :isBinary false :isTemp false :hasAggs false
> :hasSubLinks false :rtable <> :jointree <> :rowMarks () :targetList <>
> :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
> DEBUG:  ProcessUtility: set datestyle to 'ISO'; select version(), case when
> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
> getdatabaseencoding() end;
> DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 :into
> <>:isPortal false :isBinary false :isTemp false :hasAggs false
> :hasSubLinks false :rtable <> :jointree { FROMEXPR :fromlist <> :quals <>}
> :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
> 25:restypmod -1 :resname version :reskey 0 :reskeyop 0 :ressortgroupref 0
> :resjunk false } :expr { EXPR :typeOid 25  :opType func :oper { FUNC
> :funcid 89 :functype 25 } :args <>}} { TARGETENTRY :resdom { RESDOM :resno
> 2:restype 19 :restypmod -1 :resname getdatabaseencoding :reskey 0
> :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CASE :casetype 19
> :arg <> :args ({ WHEN { EXPR :typeOid 16  :opType op :oper { OPER :opno 93
> :opid 0 :opresulttype 16 } :args ({ EXPR :typeOid 19  :opType func :oper {
> FUNC:funcid 1597 :functype 19 } :args ({ CONST :consttype 23 :constlen 4
> :constbyval true :constisnull false :constvalue  4 [ 0 0 0 1 ] })} { CONST
> :consttype 19 :constlen 32 :constbyval false :constisnull false
> :constvalue  32 [ 83 81 76 95 65 83 67 73 73 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
> 0 0 0 0 0 0 0 0 ] })} :then { CONST :consttype 19 :constlen 32 :constbyval
> false:constisnull false :constvalue  32 [ 85 78 75 78 79 87 78 0 0 0 0 0 0
> 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ] }}) :defresult { EXPR :typeOid
> 19:opType func :oper { FUNC :funcid 1039 :functype 19 } :args <>}}})
> :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
> DEBUG:  plan: { RESULT :startup_cost 0.00 :total_cost 0.01 :rows 1 :width 0
> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25
> :restypmod -1 :resname version :reskey 0 :reskeyop 0 :ressortgroupref 0
> :resjunk false } :expr { EXPR :typeOid 25  :opType func :oper { FUNC
> :funcid 89 :functype 25 } :args <>}} { TARGETENTRY :resdom { RESDOM :resno
> 2:restype 19 :restypmod -1 :resname getdatabaseencoding :reskey 0
> :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { CASE :casetype 19
> :arg <> :args ({ WHEN { EXPR :typeOid 16  :opType op :oper { OPER :opno 93
> :opid 62 :opresulttype 16 } :args ({ EXPR :typeOid 19  :opType func :oper {
> FUNC:funcid 1597 :functype 19 } :args ({ CONST :consttype 23 :constlen 4
> :constbyval true :constisnull false :constvalue  4 [ 0 0 0 1 ] })} { CONST
> :consttype 19 :constlen 32 :constbyval false :constisnull false
> :constvalue  32 [ 83 81 76 95 65 83 67 73 73 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
> 0 0 0 0 0 0 0 0 ] })} :then { CONST :consttype 19 :constlen 32 :constbyval
> false:constisnull false :constvalue  32 [ 85 78 75 78 79 87 78 0 0 0 0 0 0
> 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ] }}) :defresult { EXPR :typeOid
> 19:opType func :oper { FUNC :funcid 1039 :functype 19 } :args <>}}})
> :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
> :nprm 0  :resconstantqual <>}
> DEBUG:  ProcessQuery
> DEBUG:  CommitTransactionCommand
> DEBUG:  StartTransactionCommand
> DEBUG:  query: select contact.url from person, department, company,
> contact, country where person.person_id = 4 and person.department_id =
> department.department_id and department.company_id = company.company_id and
> contact.country_id = country.country_id and person.contact_id =
> contact.contact_id
> DEBUG:  parse tree: { QUERY :command 1  :utility <> :resultRelation 0 :into
> <>:isPortal false :isBinary false :isTemp false :hasAggs false
> :hasSubLinks false :rtable ({ RTE :relname person :relid 16965  :subquery
> <>:alias <> :eref { ATTR :relname person :attrs (
> "person_id"   "is_nokis_contact"   "first_name"   "last_name"   "title"
> "position"   "contact_id"   "phone"   "private_phone"   "cellphone"   "fax"
>    "department_id"   "email" )} :inh true :inFromCl true :checkForRead true
> :checkForWrite false :checkAsUser 0} { RTE :relname department :relid
> 16945:subquery <> :alias <> :eref { ATTR :relname department :attrs (
> "department_id"   "name_de"   "contact_id"   "name_en"   "company_id" )}
> :inh true :inFromCl true :checkForRead true :checkForWrite false
> :checkAsUser 0} { RTE :relname company :relid 16900  :subquery <> :alias <>
> :eref { ATTR :relname company :attrs (
> "company_id"   "name_1_de"   "name_2_de"   "acronym_de"   "contact_id"
> "name_1_en"   "name_2_en"   "acronym_en" )} :inh true :inFromCl true
> :checkForRead true :checkForWrite false :checkAsUser 0} { RTE :relname
> contact:relid 16918  :subquery <> :alias <> :eref { ATTR :relname contact
> :attrs (
> "contact_id"   "street_1"   "street_2"   "plz_street"   "city_street"
> "postbox"   "plz_postbox"   "city_postbox"   "country_id"   "url"   "email"
>    "phone"   "fax"   "cellphone" )} :inh true :inFromCl true :checkForRead
> true:checkForWrite false :checkAsUser 0} { RTE :relname country :relid
> 16938:subquery <> :alias <> :eref { ATTR :relname country :attrs (
> "country_id"   "iso_acronym"   "name_de"   "name_en" )} :inh true :inFromCl
> true:checkForRead true :checkForWrite false :checkAsUser 0}) :jointree {
> FROMEXPR:fromlist ({ RANGETBLREF 1 } { RANGETBLREF 2 } { RANGETBLREF 3 } {
> RANGETBLREF 4 } { RANGETBLREF 5 }) :quals { EXPR :typeOid 16  :opType and
> :oper <> :args ({ EXPR :typeOid 16  :opType and :oper <> :args ({ EXPR
> :typeOid 16  :opType and :oper <> :args ({ EXPR :typeOid 16  :opType and
> :oper <> :args ({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid
> 0:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype
> 23:constlen 4 :constbyval true :constisnull false :constvalue  4 [ 0 0 0 4
> ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 0
> :opresulttype 16 } :args ({ VAR :varno 1 :varattno 12 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12} { VAR :varno 2
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
> :varoattno 1})})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
> :opid 0 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 5 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5} { VAR :varno 3
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3
> :varoattno 1})})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
> :opid 0 :opresulttype 16 } :args ({ VAR :varno 4 :varattno 9 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9} { VAR :varno 5
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5
> :varoattno 1})})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
> :opid 0 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7} { VAR :varno 4
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4
> :varoattno 1})})}} :rowMarks () :targetList ({ TARGETENTRY :resdom { RESDOM
> :resno 1 :restype 1043 :restypmod 84 :resname url :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 4 :varattno 10
> :vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}})
> :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
> :limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}
> DEBUG:  plan: { NESTLOOP :startup_cost 0.00 :total_cost 24.17 :rows 1
> :width 90 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
> 1043:restypmod 84 :resname url :reskey 0 :reskeyop 0 :ressortgroupref 0
> :resjunk false } :expr { VAR :varno 65001 :varattno 3 :vartype 1043
> :vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}}) :qpqual <>
> :lefttree { NESTLOOP :startup_cost 0.00 :total_cost 19.33 :rows 1 :width 86
> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
> RESDOM:resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 2
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}} {
> TARGETENTRY:resdom { RESDOM :resno 3 :restype 1043 :restypmod 84 :resname
> <>:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
> :varno 65001 :varattno 3 :vartype 1043 :vartypmod 84  :varlevelsup 0
> :varnoold 4 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 4
> :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 4
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9}} {
> TARGETENTRY:resdom { RESDOM :resno 5 :restype 23 :restypmod -1 :resname <>
> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
> :varno 65001 :varattno 5 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 4 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype
> 23:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
> :resjunk false } :expr { VAR :varno 65001 :varattno 6 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY
> :resdom { RESDOM :resno 7 :restype 23 :restypmod -1 :resname <> :reskey 0
> :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001
> :varattno 7 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
> :varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 65000 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 3 :varoattno 1}}) :qpqual <> :lefttree {
> NESTLOOP:startup_cost 0.00 :total_cost 14.49 :rows 1 :width 82
> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
> RESDOM:resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 2
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}} {
> TARGETENTRY:resdom { RESDOM :resno 3 :restype 1043 :restypmod 84 :resname
> <>:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
> :varno 65001 :varattno 3 :vartype 1043 :vartypmod 84  :varlevelsup 0
> :varnoold 4 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 4
> :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 4
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9}} {
> TARGETENTRY:resdom { RESDOM :resno 5 :restype 23 :restypmod -1 :resname <>
> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
> :varno 65001 :varattno 5 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 4 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype
> 23:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
> :resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY
> :resdom { RESDOM :resno 7 :restype 23 :restypmod -1 :resname <> :reskey 0
> :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000
> :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
> :varoattno 5}}) :qpqual <> :lefttree { NESTLOOP :startup_cost 0.00
> :total_cost 9.66 :rows 1 :width 74 :qptargetlist ({ TARGETENTRY :resdom {
> RESDOM:resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 1
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12}} {
> TARGETENTRY:resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname <>
> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
> :varno 65001 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 1 :varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype
> 1043:restypmod 84 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
> :resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 1043
> :vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}} { TARGETENTRY
> :resdom { RESDOM :resno 4 :restype 23 :restypmod -1 :resname <> :reskey 0
> :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000
> :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4
> :varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 65000 :varattno 3 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 4 :varoattno 1}}) :qpqual <> :lefttree {
> INDEXSCAN:startup_cost 0.00 :total_cost 4.82 :rows 1 :width 8
> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 1 :varattno 12 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY :resdom {
> RESDOM:resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 7
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}})
> :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
> :nprm 0  :scanrelid 1 :indxid ( 16970) :indxqual (({ EXPR :typeOid
> 16:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
> VAR:varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval
> true:constisnull false :constvalue  4 [ 0 0 0 4 ] })})) :indxqualorig (({
> EXPR:typeOid 16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype
> 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23
> :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 0 0 0 4 ]
> })})):indxorderdir 1 } :righttree { INDEXSCAN :startup_cost 0.00
> :total_cost 4.82 :rows 1 :width 66 :qptargetlist ({ TARGETENTRY :resdom {
> RESDOM:resno 1 :restype 1043 :restypmod 84 :resname <> :reskey 0 :reskeyop
> 0:ressortgroupref 0 :resjunk false } :expr { VAR :varno 4 :varattno 10
> :vartype 1043 :vartypmod 84  :varlevelsup 0 :varnoold 4 :varoattno 10}} {
> TARGETENTRY:resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname <>
> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
> :varno 4 :varattno 9 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4
> :varoattno 9}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 4 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 4 :varoattno 1}}) :qpqual <> :lefttree <>
> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 4
> :indxid ( 16920) :indxqual (({ EXPR :typeOid 16  :opType op :oper { OPER
> :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 4 :varattno 1
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 1} { VAR
> :varno 65001 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 1 :varoattno 7})})) :indxqualorig (({ EXPR :typeOid 16  :opType
> op:oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno
> 65001:varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
> :varoattno 7} { VAR :varno 4 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 4 :varoattno 1})})) :indxorderdir 0 } :extprm
> ():locprm () :initplan <> :nprm 0  :jointype 0 :joinqual <>} :righttree {
> INDEXSCAN:startup_cost 0.00 :total_cost 4.82 :rows 1 :width 8
> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
> :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
> false } :expr { VAR :varno 2 :varattno 1 :vartype 23 :vartypmod
> -1:varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY :resdom {
> RESDOM:resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
> :ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 5
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5}})
> :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
> :nprm 0  :scanrelid 2 :indxid ( 16947) :indxqual (({ EXPR :typeOid
> 16:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
> VAR:varno 2 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 2 :varoattno 1} { VAR :varno 65001 :varattno 1 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12})})) :indxqualorig
> (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
> :opresulttype 16 } :args ({ VAR :varno 65001 :varattno 1 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 12} { VAR :varno 2
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
> :varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
> :nprm 0  :jointype 0 :joinqual <>} :righttree { INDEXSCAN :startup_cost
> 0.00:total_cost 4.82 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom
> { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
> 0:ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 1}})
> :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
> :nprm 0  :scanrelid 3 :indxid ( 16903) :indxqual (({ EXPR :typeOid
> 16:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
> VAR:varno 3 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 3 :varoattno 1} { VAR :varno 65001 :varattno 7 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5})})) :indxqualorig
> (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
> :opresulttype 16 } :args ({ VAR :varno 65001 :varattno 7 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 5} { VAR :varno 3
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3
> :varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
> :nprm 0  :jointype 0 :joinqual <>} :righttree { INDEXSCAN :startup_cost
> 0.00:total_cost 4.82 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom
> { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
> 0:ressortgroupref 0 :resjunk false } :expr { VAR :varno 5 :varattno 1
> :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 :varoattno 1}})
> :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
> :nprm 0  :scanrelid 5 :indxid ( 16940) :indxqual (({ EXPR :typeOid
> 16:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
> VAR:varno 5 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
> :varnoold 5 :varoattno 1} { VAR :varno 65001 :varattno 4 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9})})) :indxqualorig
> (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
> :opresulttype 16 } :args ({ VAR :varno 65001 :varattno 4 :vartype 23
> :vartypmod -1  :varlevelsup 0 :varnoold 4 :varoattno 9} { VAR :varno 5
> :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5
> :varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
> :nprm 0  :jointype 0 :joinqual <>}
> -----------------------------------------------------------
> Dipl. Geogr. Carsten Heidmann
> Tel.: 040.81908.345
> Fax: 040.81908.373
> heidmann@hamburg.baw.de
> http://nokis.baw.de/
>
> Bundesanstalt fuer Wasserbau
> - Aussenstelle Kueste -
> Wedeler Landstrasse 157
> DE - 22559 Hamburg
> -----------------------------------------------------------
>
>

Re: Problems with column names in dot-notation

От
Carsten Heidmann
Дата:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Dave,
You are aware that postgres will return postgres will return contact.url
into a column named url?

Thanks for that! I ported my application from MySQL and MySQL returned the
full designator.

Regards, Carsten



-----BEGIN PGP SIGNATURE-----
Version: 6.5.8ckt

iQA/AwUBPVpVzLjxzYr/SJ6uEQKr2QCaAtcQeyTHJtYDFNYwPSBZI2/X6SAAoKgj
aTevpN/6/XjA3yT9H7AAt8Gb
=w3+S
-----END PGP SIGNATURE-----
-----------------------------------------------------------
Dipl. Geogr. Carsten Heidmann
Tel.: 040.81908.345
Fax: 040.81908.373
heidmann@hamburg.baw.de
http://nokis.baw.de/

Bundesanstalt fuer Wasserbau
- Aussenstelle Kueste -
Wedeler Landstrasse 157
DE - 22559 Hamburg
-----------------------------------------------------------