Re: transaction problem using cursors

Thank you Pavel!

The problem is, that the CAST function may be used in any SQL query,
since our software allows using a free SQL-query. The query itself
doesn't fail, because the syntax is correct.
The problem is that there may be other functions in a query that can
lead to a runtime error during a FETCH.
The goal is to maintain a valid cursor that can be used to FETCH other
records.
It would be ideal to skip this record an continue fetching the next
record -> but how can i achieve this?

Thanks

Pit



> It's not good advice. I tested it, and problem is in where clause. I
> don't understand problem well, but one possibility is change from cast
> to to_number function like:
>
> postgres=# begin;
> BEGIN
> postgres=# declare c cursor for select * from fx where
> to_number(b,'99999') > 0;
> DECLARE CURSOR
> postgres=# fetch from c;
> a  | b
> ----+----
> 10 | 20
> (1 row)
>
> postgres=# fetch from c;
> a  |  b
> ----+-----
> 10 | a20
> (1 row)
>
> But still this solution is +/- correct
>
> Regards
> Pavel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>