View Issue Details

IDProjectCategoryView StatusLast Update
0014251FPCDatabasepublic2014-02-19 06:27
ReporterEvgeniy Semenov Assigned ToMichael Van Canneyt  
PrioritynormalSeverityfeatureReproducibilityalways
Status resolvedResolutionfixed 
Platformx86OSUbuntu Linux 
Product Version2.3.1 
Target Version3.0.0Fixed in Version3.0.0 
Summary0014251: TSQLQuery ignore ENUM type fields in PostgreSQL 8.3
DescriptionAfter 'SELECT * FROM tablename' query, SQLQuery.Fields does not contain a fields with type 'ENUM'. I've tested this issue with TSQLQuery and with ZEOS TZQuery.
TagsNo tags attached.
Fixed in Revision26804
FPCOldBugId
FPCTarget
Attached Files

Activities

Felipe Monteiro de Carvalho

2010-07-21 09:21

developer   ~0039536

Please provide a minimal full compilable example including the database if necessary.

LacaK

2010-07-21 10:42

developer   ~0039545

Last edited: 2010-11-11 08:33

It seems, that problem is in fact, that ENUM datatypes does not have predefined built-in OID, so in function TPQConnection.TranslateFldType is ftUnknown returned (used in AddFieldDefs)
and later in function TPQConnection.LoadField such field is ignored.

See also: http://www.postgresql.org/docs/8.4/static/catalog-pg-type.html
(column typtype = 'e' and typcategory='E' for ENUM types) and http://www.postgresql.org/docs/8.4/static/catalog-pg-enum.html and
http://doxygen.postgresql.org/include_2catalog_2pg__type_8h-source.html#l00622 )

We can cast "enum fields" to char/varchar in selects using cast(enum_field to varchar(...)) or enum_field::varchar(...), but such fields are not updatable later
(PREPARE st1 (varchar(10), bigint) AS update table1 set enum_field=$1 where id_field=$2; ... fails "column "enum_field" is of type enum_type but expression is of type character varying" ... this error we can avoid using:
PREPARE st1 (unknown, bigint) AS update table1 set enum_field=$1 where id_field=$2;
EXECUTE st1 ('option1',1);
-or cast-
PREPARE st1 (varchar(10), bigint) AS update table1 set enum_field=$1::enum_type where id_field=$2;
EXECUTE st1 ('option1',1);
)

IMHO for full handling of user-defined types/domains we must query system table pg_type to get "base types"

Michael Van Canneyt

2014-02-17 07:42

administrator   ~0073106

Enum fields are now converted to string fields, 64 long.
They are updateable.

Issue History

Date Modified Username Field Change
2009-08-02 19:25 Evgeniy Semenov New Issue
2009-08-02 19:25 Evgeniy Semenov Status new => assigned
2009-08-02 19:25 Evgeniy Semenov Assigned To => Joost van der Sluis
2010-07-21 09:21 Felipe Monteiro de Carvalho Note Added: 0039536
2010-07-21 09:21 Felipe Monteiro de Carvalho Status assigned => feedback
2010-07-21 10:42 LacaK Note Added: 0039545
2010-07-21 12:21 LacaK Note Edited: 0039545
2010-07-21 12:22 LacaK Note Edited: 0039545
2010-11-11 08:13 LacaK Note Edited: 0039545
2010-11-11 08:33 LacaK Note Edited: 0039545
2014-02-17 07:42 Michael Van Canneyt Fixed in Revision => 26804
2014-02-17 07:42 Michael Van Canneyt Note Added: 0073106
2014-02-17 07:42 Michael Van Canneyt Status feedback => resolved
2014-02-17 07:42 Michael Van Canneyt Fixed in Version => 2.7.1
2014-02-17 07:42 Michael Van Canneyt Resolution open => fixed
2014-02-17 07:42 Michael Van Canneyt Target Version => 2.8.0
2014-02-17 07:42 Michael Van Canneyt Assigned To Joost van der Sluis => Michael Van Canneyt
2014-02-17 07:42 Michael Van Canneyt Status resolved => assigned
2014-02-18 15:25 Michael Van Canneyt Status assigned => resolved