Postgres Pro
Downloads
Home   >   mailing lists

Thread: PostgreSQL does not support updateable cursors

PostgreSQL does not support updateable cursors

From
"Premsun Choltanwanich"
Date:
Dear All,
 
     Regarding to my conversation on mailing list as shown below, Is my problem related with any function on psqlODBC ? And, How can I fix it?
 
Thank you,
 
Premsun
 
>>> "Pavel Stehule" <pavel.stehule@gmail.com> 2/12/2008 14:04 >>>
Hello
 
PostgreSQL support updateable cursors but doesn't support updateable
views - so you cannot update views via ADO recordset. You can update
only tables.
 
Regards
Pavel Stehule
 
On 12/02/2008, Premsun Choltanwanich <Premsun@nsasia.co.th> wrote:
>
>
> Dear All,
>
>      I got error message 'ERROR: column "ctid" does not exist; Error while executing the query'  when I try to query SELECT on my VIEW as 'rsSystem.Open "SELECT * FROM v_memocatlist ORDER BY memocategory", connSystem, adOpenStatic, adLockOptimistic'.
>
>      I found some information on internet about the PostgreSQL does not support updateable cursors so I change my code to be read only cursors as  'rsSystem.Open "SELECT * FROM v_memocatlist ORDER BY memocategory", connSystem, adOpenForwardOnly, adLockReadOnly' then it work fine. However, I need to use this query for make data ready to be updated on some record.
>
>      How can I fix this problem?
>
> More Information about my system.
> Database: PostgreSQL 8.3
> ODBC: psqlODBC 08.03.0100
> Language: MS Visual Basic 6.0
>
> Regards,
> Premsun
>
>
>        NETsolutions Asia        Limited
>
>        +66 (2) 237 7247
>
>
>

NETsolutions Asia Limited

+66 (2) 237 7247

NETsolutions Asia Limited

Attachment

Re: PostgreSQL does not support updateable cursors

From
"Richard Broersma"
Date:
On Sun, Mar 2, 2008 at 11:06 PM, Premsun Choltanwanich <Premsun@nsasia.co.th> wrote:
Dear All,
 
     Regarding to my conversation on mailing list as shown below, Is my problem related with any function on psqlODBC ? And, How can I fix it?
 
No, the problem is not with the ODBC driver.  The problem is that views in postgresql are static non-update-able by default.  So if you try to update and record in a view using a cursor or any other sql statement, you will get an error.

This problem can be fixed if your view meets the following criteria:

1) your view is based on only one table

2) your create rules for your view that define how updates to the view are to be handled/cascaded to the base table.
http://www.postgresql.org/docs/8.3/interactive/sql-createrule.html

3) since postgresql rules can only handle update statements that affect only one tuple at a time, your cursor must only be able to update one record at a time.


Regards,
Richard Broersma Jr.