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
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