Invalid input for integer on VIEW

On Tue, 2004-08-24 at 15:42 +0100, mike wrote:

I have the following view definition

Column | Type | Modifiers
----------------+-----------------------+-----------
bcode | character varying(15) |
subhead | text |
sc_description | character varying(60) |
Budget | numeric |
expenditure | numeric |
balance | numeric |
head | integer |
period | integer |
View definition:
SELECT
CASE
WHEN vw_rec_sum.code IS NULL AND vw_pay_sum.sum IS NOT NULL
THEN vw_pay_sum.code
ELSE vw_rec_sum.code
END AS bcode,
CASE
WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 'Salary
Costs'::text
WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 'Startup
Costs'::text
WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 'Running
Costs'::text
WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 'Training
Costs'::text
ELSE NULL::text
END AS subhead, sc_description, vw_rec_sum.sum AS "Budget",
vw_pay_sum.sum AS expenditure,
CASE
WHEN vw_pay_sum.sum IS NULL THEN vw_rec_sum.sum
WHEN vw_pay_sum.sum < 0.01 THEN 0.00 - vw_pay_sum.sum +
vw_rec_sum.sum
ELSE vw_rec_sum.sum - vw_pay_sum.sum
END AS balance,
CASE
WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 1
WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 2
WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 3
WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 4
ELSE NULL::integer
END AS head,
CASE
WHEN to_number(vw_rec_sum.code::text, '999'::text) >
194::numeric THEN 3
WHEN to_number(vw_rec_sum.code::text, '999'::text) <
195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) >
50::numeric THEN 2
ELSE 1
END AS period
FROM vw_rec_sum
FULL JOIN vw_pay_sum ON vw_rec_sum.code::text = vw_pay_sum.code::text
JOIN vw_ac ON vw_rec_sum.code::text = vw_ac.id::text
ORDER BY to_number(vw_rec_sum.code::text, '999'::text);

However whenever I try to query it with criteria on the period column I
get SELECT * FROM vw_budget HAVING "period"='3';
ERROR: invalid input syntax for type numeric: " "

If I try on the head column the query runs

Getting stumped - anyone any idea what is going on here.

This is with 7.4.3

If I do the same query, except to create a new table, everything works,
so is this a view bug?

mike <mike@bristolreccc.co.uk> writes:

If I do the same query, except to create a new table, everything works,
so is this a view bug?

Possibly, but you haven't given enough info to let someone else
reproduce the problem. A SQL script that creates all the necessary
tables and the view and then triggers the failure would make it much
easier for us to investigate.

regards, tom lane

On Tue, 2004-08-24 at 12:30 -0400, Tom Lane wrote:

mike <mike@bristolreccc.co.uk> writes:

If I do the same query, except to create a new table, everything works,
so is this a view bug?

Possibly, but you haven't given enough info to let someone else
reproduce the problem. A SQL script that creates all the necessary
tables and the view and then triggers the failure would make it much
easier for us to investigate.

regards, tom lane

Is this OK, or do you want some data as well?

mike <mike@bristolreccc.co.uk> writes:

On Tue, 2004-08-24 at 12:30 -0400, Tom Lane wrote:

Possibly, but you haven't given enough info to let someone else
reproduce the problem.

Is this OK, or do you want some data as well?

I plugged in the view definition from your original mail and got

regression=# SELECT * FROM vw_budget HAVING "period"='3';
bcode | subhead | sc_description | Budget | expenditure | balance | head | period
-------+---------+----------------+--------+-------------+---------+------+--------
(0 rows)

So either the problem requires data, or there is something wrong with
your left() function, which was not included in the script. I guessed
at

create function left(text, integer) returns text as
'select substr($1,$2)' language sql ;

but I dunno if this is anything like what you are using.

Please actually *test* the script you are submitting, next time, and
verify that it reproduces the problem starting from an empty database.

regards, tom lane