boolean, array types not handled reasonably on PostGIS layers

[smizuno]

A PostGIS table containing a boolean or any array type (also some other types) causes an empty Attribute table (rows/columns, but no data) and "No features found" using Identify. This worked in 0.8.1.

The boolean type is often quite useful.

The problem is that a cast to text is used when building the binary cursor query in QgsPostgresProvider, select() and getFeatureAtId() functions, but there are no such casts to text in PostgreSQL for these types, so the query causes a database error, which is not reported to the user.

Reasonable handling would be to use the default string handling in QGIS for any type not specifically handled. This works as long as there is a textual return from the database like there is with a non-binary cursor. Even editing will work.

Interestingly, PostGIS provides a boolean to text function, so a boolean type column could be requested using text(boolean_column) rather than boolean_column::text.

A work-around is to create suitable type-to-text functions and casts on the database so the ::text cast is available for these types.

I am using PostgreSQL 8.2.6 / PostGIS 1.3.2

[jef]

fixed in r8285. Please test.

[smizuno]

Replying to jef:

fixed in r8285. Please test.

I have tested with r8290 and it works as expected for boolean type. Array and money types just show blank cells where there should be values. Even NULL is not displayed for null values.

Since I posted this defect I have come up with a possible solution for handling types that don't have a cast to text. This should fit with the revised PostGIS provider. However, this may have PostgreSQL version differences as the functions are not published in the documentation.

Use a function that outputs a cstring for a specified type. I believe that the return from the query is actually a cstring, so there is no need to get the text type. Also be sure to use a column alias to identify the column.

Examples:

boolean type: boolout("column_name") as "column_name"

money type: cash_out("column_name) as "column_name"

array type: array_out("column_name") as "column_name"

array_out should work for any array type, but I have tested only with integer (int4, int8) and text arrays. There is also an anyarray_out() function, but I don't see any significant difference.

If there is a problem handling cstrings the output functions above can be wrapped with a textin() function to get to a text type. I don't believe that there is such a problem, but different versions of PostgreSQL may have slightly different behavior.

I have attached a patch incorporating the ...out() functions for money, array, and boolean types.

I have tested (but not extensively) the patch on boolean and money types, and integer and text arrays. Identify, Attribute table (display and edit), Feature editing all work.

[smizuno]

I'm sorry - please disregard the part about NULL is not displayed for null values. I rechecked the types and found that NULL is displayed.

[(none)]

Milestone Version 0.9.2 deleted