Jump to content

- - - - -

Views in PostGreSQL

4 replies to this topic

#1 Guest_Archie Mulder_*

Guest_Archie Mulder_*
  • Guests

Posted 04 May 2006 - 09:10 PM

We are not able to get data from views in ProIV from a postGreSQL database.

according to the Pro 4 SQL trace Pro 4 is attempting to retrieve a system column named ctid that does not exist for views.

Version 5.5 R. 4.1.1 of pro 4 running postgresql version 8.0

Any help would be appreciated.


#2 Guest_GUEST 23_*

Guest_GUEST 23_*
  • Guests

Posted 05 May 2006 - 09:36 AM

Can you add a pseudo column to your view

#3 Guest_Schweik_*

  • Guests

Posted 05 May 2006 - 03:27 PM

Are you trying to write to the view?

#4 Guest_Guest_*

  • Guests

Posted 05 May 2006 - 05:38 PM

(Can you add a pseudo column to your view ) .. tried did not work.

( Are you trying to write to the view? ) ... no ... all is in lookup mode

#5 mdexter



  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 23 May 2006 - 04:44 PM

First of all, to use views your PostGres tables must be defined with OID's. This used to be the default in the create table but I think it might not be with V8. Second, your view must then include the columns OID and CTID from one of the tables referenced in the view (normally the "primary" table in the view, so that they will be unique for each row of the view).

For some reason, when PRO-IV creates a cursor for Postgres it requires these columns to be present. Since all PRO-IV access to SQL tables is via a cursor, these columns must be present in any view accessed by PRO-IV. I don't know whether this is because of the way the ODBC driver works or is something from PRO-IV. If you look at the PostGres trace, you will see two SQL commands to find the row -- the second one uses CTID or OID or both (don't remember the details).

Also, if you want the view to be updateable, in addition to the normal rules for updateable views, you need to create Rules in Postgres -- one for UPDATE, one for DELETE, and one for INSERT. You can look in the Postgres documentation for details on this. These rules then allow PRO-IV to treat views as tables, including writing back to the views.

Hope this helps. Mark Dexter

Reply to this topic


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users