Received: from relay.ipf.net (relay.ipf.net [195.211.211.23]) by hub.org (8.9.3/8.9.3) with SMTP id EAA75201 for ; Wed, 9 Jun 1999 04:26:06 -0400 (EDT) (envelope-from klemme@bankakademie.de) Received: (qmail 50035 invoked from network); 9 Jun 1999 08:26:05 -0000 Received: from mail.ipf.de (HELO mail2.ipf.de) (195.211.211.22) by relay.ipf.net with SMTP; 9 Jun 1999 08:26:05 -0000 Received: from orakel.bankakademie.de ([195.88.70.125]) by mail2.ipf.de (Netscape Mail Server v2.02) with ESMTP id AAA22148 for ; Wed, 9 Jun 1999 10:26:04 +0200 Received: from bankakademie.de (really [195.88.70.42]) by orakel.bankakademie.de via smail with esmtp id (Debian Smail3.2.0.101) for ; Wed, 9 Jun 1999 10:26:20 +0200 (CEST) Message-ID: <375E25AE.EEA6FD67@bankakademie.de> Date: Wed, 09 Jun 1999 10:28:30 +0200 From: Michael Klemme Reply-To: mklemme@gmx.de Organization: Bankakademie e.V. X-Mailer: Mozilla 4.6 [en] (WinNT; I) X-Accept-Language: de,en,nl,fr MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: SQL Problem: ERROR: ExecEvalExpr: unknown expression type 108 Content-Type: multipart/mixed; boundary="------------A04DB69946198F0346243A98" This is a multi-part message in MIME format. --------------A04DB69946198F0346243A98 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hallo! I try to create a web server authentification database for our Apache using PostgreSQL. Students will be added to certain groups, group entries need to be invalidated once in a while. klemme@moa:~/Postgres/auth> psql -d authentify -f auth-create-test.sql DROP TABLE user; DROP DROP TABLE ugroup; DROP -- user entries CREATE TABLE user( vorname char(20) NOT NULL, --Vorname des Benutzers name char(20) NOT NULL, --Name des Benutzers uname char(10) PRIMARY KEY, --eindeutiger Benutzername des Benutzers im Server'; pass char(16) NOT NULL, --Paßwort des Benutzers changed date NOT NULL ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index user_pkey for table userCREATE -- group entries CREATE TABLE ugroup ( uname char(10) NOT NULL, --Zeiger auf user ugroup char(10) NOT NULL, --Name der Gruppe expires date NOT NULL, --Verfallsdatum changed date NOT NULL); CREATE CREATE UNIQUE INDEX ugroup_idx ON ugroup (uname,ugroup); CREATE vacuum; VACUUM --- Test entries insert into user values ('Michael', 'Klemme', 'mklemme', 'xxxx', '04-06-1999'); INSERT 24895 1 insert into user values ('Michaela','Klemme','maklemme', 'xxxx', '04-06-1999'); INSERT 24896 1 insert into ugroup values ('mklemme', 'Physics', '31-12-1999', '04-06-1999'); INSERT 24897 1 insert into ugroup values ('mklemme', 'Math', '31-12-1999', '04-06-1999'); INSERT 24898 1 insert into ugroup values ('maklemme', 'Math', '31-12-1998', '04-06-1999'); INSERT 24899 1 --I need to create a view for the Apache module that only returns user --entries if a non-expired group entry exists. --The SELECT Statement below works okay. SELECT uname, pass FROM user WHERE UNAME IN (SELECT uname FROM ugroup WHERE expires >= datetime_date('now')); uname |pass ----------+---------------- mklemme |xxxx (1 row) --However, if I use the same statment in a view, I get this error message -- view to be accessed by Apache DROP VIEW legal_user; DROP CREATE VIEW legal_user AS SELECT uname, pass FROM user WHERE UNAME IN (SELECT uname FROM ugroup WHERE expires >= datetime_date('now')); CREATE SELECT * FROM legal_user; ERROR: ExecEvalExpr: unknown expression type 108 EOF ANy help would be greatly appreciated! Thanks, Grüße Michael -- Michael Klemme @home mklemme@gmx.de / klemme@bankakademie.de / klemme@acm.org PGP: http://www.hmu.auckland.ac.nz/~michael-k/pgp --------------A04DB69946198F0346243A98 Content-Type: text/plain; charset=us-ascii; name="auth-create-test.sql" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="auth-create-test.sql" DROP TABLE user; DROP TABLE ugroup; -- user entries CREATE TABLE user ( vorname char(20) NOT NULL, --Vorname des Benutzers name char(20) NOT NULL, --Name des Benutzers uname char(10) PRIMARY KEY, --eindeutiger Benutzername des Benutzers im Server'; pass char(16) NOT NULL, --Paßwort des Benutzers changed date NOT NULL ); -- group entries CREATE TABLE ugroup ( uname char(10) NOT NULL, --Zeiger auf user ugroup char(10) NOT NULL, --Name der Gruppe expires date NOT NULL, --Verfallsdatum changed date NOT NULL ); CREATE UNIQUE INDEX ugroup_idx ON ugroup (uname,ugroup); vacuum; --- Test entries insert into user values ('Michael', 'Klemme', 'mklemme', 'xxxx', '04-06-1999'); insert into user values ('Michaela','Klemme','maklemme', 'xxxx', '04-06-1999'); insert into ugroup values ('mklemme', 'Physics', '31-12-1999', '04-06-1999'); insert into ugroup values ('mklemme', 'Math', '31-12-1999', '04-06-1999'); insert into ugroup values ('maklemme', 'Math', '31-12-1998', '04-06-1999'); --I need to create a view for the Apache module that only returns user --entries if a non-expired group entry exists. --The SELECT Statement below works okay. SELECT uname, pass FROM user WHERE UNAME IN (SELECT uname FROM ugroup WHERE expires >= datetime_date('now')); --However, if I use the same statment in a view, I get this error message -- view to be accessed by Apache DROP VIEW legal_user; CREATE VIEW legal_user AS SELECT uname, pass FROM user WHERE UNAME IN (SELECT uname FROM ugroup WHERE expires >= datetime_date('now')); SELECT * FROM legal_user; --------------A04DB69946198F0346243A98--