We have encountered a pretty oddball situation involving an "unknown" type.
mydb=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.2 on 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
