Re: (resend) NULL constrains inheritance to views

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>