We have encountered a pretty oddball situation involving an "unknown" type.
mydb=# select version(); version
----------------------------------------------------------------------------------------------------------PostgreSQL
7.4.2on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-24)
(1 row)
mydb=# \d redact_current24248 Table "public.redact_current24248" Column | Type | Modifiers
-------------------------+-----------+-----------n_posted_transaction_id | integer | n_year_u |
"unknown"| n_month_u | "unknown" | n_breakdown_config_id | integer | n_amount |
numeric |
We'd like to turn those "unknown" values into plain integers (e.g. -
years and months); apparently it's not so simple...
mydb=# select n_year_u::integer, n_month_u::integer from redact_current24248 limit 10;
ERROR: failed to find conversion function from "unknown" to integer
How this was generated was with Perl code where the prepared query
looks something like the following:
"CREATE TEMP TABLE $tableName AS ".
"SELECT a.id as n_posted_transaction_id, ". "? as n_year_u, ". "? as n_month_u, ". "c.id as
n_breakdown_config_id,". "calc_revenue( various_parameters ) as n_amount ".
"FROM ". "transactions_posted a, ". "items b, ". "transaction_breakdown_config c; ";
I wasn't aware of there being an "unknown" type, and it's rather
bizarre that this is happening.
I imagine that specifying
"SELECT a.id as n_posted_transaction_id, ". "?::integer as n_year_u, ". "?::integer as n_month_u, ".
would likely clear this up, but where "unknown" came from is something
of a mystery. The source types shouldn't be any mystery.
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/x.html
str->str_pok |= SP_FBM; /* deep magic */
s = (unsigned char*)(str->str_ptr); /* deeper magic */
-- Larry Wall in util.c from the perl source code