pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Bertheau <twanger@bluetwanger.de>
To: Bradley Kieser <brad@kieser.net>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: (resend) NULL constrains inheritance to views
Date: Fri, 07 Jan 2005 08:33:01 +0100
Message-ID: <1105083181.3525.1.camel@fc3> (raw)
In-Reply-To: <41DD6FE6.1090306@kieser.net>
References: <41DD6FE6.1090306@kieser.net>

You could probably construct the views manually by creating tables with
a ON SELECT rule.

У Чтв, 2005-01-06 у 17:05 +0000, Bradley Kieser пише:
> (resend as my other mail seems to not have gotten through)
> 
> I have created a schema with views on top of tables. We use JDBC to 
> connect to the schema and we use JBuilderX to develop our app.
> 
> Every table as an "id" column of type serial. I.e. It is not null with 
> default nextval(...).
> However, it seems that when a column from a table is used in a view, the 
> not null part (as well as the default values) seems to be dropped.
> Serials become integers.
> 
> I can use "alter table" to restore default values on the views, but 
> "alter table" to make a view column not null results in an error saying 
> that the viewname is not a table! I.e. I don't seem to be able to make 
> the column not null.
> 
> I think that this is throwing JBuilder's resolver into chaos because it 
> doesn't use the column that I have specified as ROWID (column "id" for 
> each view) to resolve update rows back to the view, it tries to use 
> every single column, a sure sign that it thinks that the column I have 
> told it is a rowid isn't up to the job. I am pretty certain that it's 
> because it thinks that column can be null.
> 
> Unfortunately I have to use views and cannot use tables because of 
> security in the app. The views have rules set on them as well as 
> permissions to specific users. So the answer isn't to open up the 
> database and use the underlying tables instead. That would open a 
> security hole in the app. Moreover, I use views to do complex processing 
> in PG (e.g. multi-table joins or rules and triggers on updates to ensure 
> data integrity and implement business logic). JBuilder, being a RAD is 
> great at building apps from simple datasets (i.e. single views and 
> tables), but it quickly starts to wilt under complex parametised queries!
> 
> So for these reasons I need to find a way to tell JBuilder (through 
> JDBC) that the ID columns are not nullable. But how? Can someone please 
> help me?
> 
> Thanks,
> 
> Brad
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
Markus Bertheau <twanger@bluetwanger.de>

Attachments:

  signature.asc (application/pgp-signature, 188B) download

view thread (2+ messages)

Message-ID: <1105083181.3525.1.camel@fc3>
Permalink:  ../1105083181.3525.1.camel@fc3/
Also on:    postgresql.org/message-id/1105083181.3525.1.camel@fc3

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: twanger@bluetwanger.de, brad@kieser.net
  Subject: Re: (resend) NULL constrains inheritance to views
  In-Reply-To: <1105083181.3525.1.camel@fc3>
  References: <41DD6FE6.1090306@kieser.net> <1105083181.3525.1.camel@fc3>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox