The problem at hand is how to return the value of a select with an "invisible" condition on the selection. The problem in the thread involved externally resolved permissions but the example we will discuss here is a less volatile condition--we will close the ability to select for a short time window. The condition is irrelevant to the technique, however, it is important to note that the permission problem can be solved in other ways using SQL permissions and views which show partial data.

In this example we'll use the following table and OK function. Notice that we want to abort the query by raising an exception if the qualification is not valid.

CREATE TABLE gb_54 (
   colone   INTEGER,
   coltwo   INTEGER,
   colthree INTEGER);
INSERT INTO gb_54 VALUES (1,2,3);
INSERT INTO gb_54 VALUES (4,5,6);
INSERT INTO gb_54 VALUES (7,8,9);

CREATE OR REPLACE FUNCTION selOK()
RETURNS BOOLEAN AS
'
BEGIN
   IF current_time < ''18:00''::time or current_time >= ''18:30''::time
   THEN
      return current_time < ''18:00''::time or current_time >= ''18:30''::time;
   ELSE
      RAISE EXCEPTION ''Select Window is Closed from 6:00pm to 6:30pm Daily'';
   END IF;
   RETURN;
END;
' language 'plpgsql';

The first though is to create a RULE on the table with a condition in the where clause:

	#= CREATE OR REPLACE RULE COND_SEL
	#- AS ON SELECT TO gb_54 where selOK();
	#- DO INSTEAD
	#-   select * from gb_54 where selOK();
	ERROR:  view rule for "gb_54" must be named "_RETURN"

	#= CREATE TABLE myview (same column list as mytab);
	#= CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
	#-   SELECT * FROM mytab;

	=# CREATE OR REPLACE RULE "_RETURN"
	=# AS ON SELECT TO gb_54
	=# DO INSTEAD
	=#	 select * from gb_54 where selOK();
	ERROR:  could not convert table "gb_54" to a view because it is not empty

Let us try another approach: a (real) view.

	=# CREATE VIEW gb_54v AS
	-#    SELECT * FROM gb_54 WHERE selOK();
	 select * from gb_54v;
	 colone | coltwo | colthree 
	--------+--------+----------
	      1 |      2 |        3
	      4 |      5 |        6
	      7 |      8 |        9
	(3 rows)

	-- some time later....

	=# select * from gb_54v;
	ERROR:  Select Window is Closed from 6:00pm to 6:30pm Daily

Sometimes we pick the hard way to do things when an easy way is in front of our faces. So to atone for choosing the hard way as a first path, there is another tact that can always be considered when a view might also work. This is particularly helpful when the logic is too complex for a view or the dependencies are not straightforward. This other tact is to use an SRF or Set Returning Function.

An SRF to perform the same function as the view above is included below. You will have to add your own complexity. This function still relies on the selOK() which will raise an exception if the qualification criteria is not met.

	CREATE TYPE gb54t AS (tone integer, ttwo integer, tthree integer);
	CREATE OR REPLACE FUNCTION gb54f()
	RETURNS setof gb54t AS
	'
	DECLARE
	   gb54rec gb54t%ROWTYPE;
	BEGIN
	   IF selOK()
	   THEN 
	      FOR gb54rec IN SELECT * FROM gb_54 LOOP
	         RETURN NEXT gb54rec;
	      END LOOP;
	      RETURN;
	   ELSE
	      RETURN;
	   END IF;
	END;
	' language 'plpgsql';

Contributors: Chris Travers chris at travelamericas.com, Tom Lane tgl at sss.pgh.pa.us

Brief notes on accessing data from older releases [GENERAL] Salvage older PostgreSQL data disk - help? 19-Dec-2003

A computer crashed as they do from time to time, unfortunately. However, the disk seems to be OK and it had an unknown older version of postgreSQL on it.

To salvage the data, first determine exactly what version the database was running by looking in the PG_VERSION file in the data ($PGDATA) directory.

Then you must install either that version, or preferably the latest minor version in that series. That means that if the database was a 7.1 database, you probably want to install 7.1.3. To see what versions there are available, go to the PostgreSQL Download Site and select your mirror and then your flavor (binary or source). You will see all of the older versions listed. Download and install the appropriate one.

Once you have an installation compatible with your data, point your installation to the data directory to be salvaged. (Don't initdb in that location, though.)

Now you have a database server pointing to your data and you can do what you want to do to use and/or salvage the data. An upgrade to the latest 7.4 version is recommended, of course.

Contributors: Conrad bogus_address at nospam.com, Andrew Sullivan andrew at libertyrms.info, Tom Lane tgl at sss.pgh.pa.us, Jonathan Bartlett johnnyb at eskimo.com

Grant/Revoke what to whom? [GENERAL] Permissions issue? 17-Dec-2003

It is tricky to create a valid user and take away all of their privileges. By "all" we mean that they should not be able to create and select from a table. This requires several steps. This first shell command creates the user "newuser" without privileges to create databases or more users. This works.

$ createuser newuser
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

$ psql chris -U postgres
chris=# revoke all on database a from newuser;
REVOKE
chris=# revoke create on database a from newuser;
REVOKE
chris=# \q

Revoking create on the databases controls the creation of schemas. But newuser did not try to create a schema, they tried to create a table in the public schema (and succeeded).

This doesn't work either for more mysterious reasons.

chris=# REVOKE ALL ON SCHEMA public FROM newuser;
REVOKE

This is not necessarily obvious. REVOKE works on those users explicitly GRANTed privileges. It is the default that PUBLIC is explicitly granted all on schema public. The special case of user PUBLIC is all users. To change this scheme you must be specific in the revocation and granting to individual users.

Contributors: Christopher Murtagh christopher.murtagh at mcgill.ca, Stephan Szabo sszabo at megazone.bigpanda.com, Tom Lane tgl at sss.pgh.pa.us

Snippets from Academia on Relational Databases [GENERAL] Relational data model dead? 15-Dec-2003

An old paper written in 1998 by Whitemarsh Information Systems has a "timely" announcement the "Great News, The Relational Model Is Dead!" The topic of the paper is the SQL99 standard, also known as SQL3. This paper solicited the pointed remarks that follow:

Tom Lane:

Unlikely; if they are of the persuasion that relational DBs are uninteresting, they'd hardly be spending time working on bigger and better(?) standards for relational DBs.

"Relational databases are dead" has been a standard academic litany for years now, but it has nothing to do with the real world AFAICS.

Maybe it means there isn't anything interesting to study about relational databases. From an academic perspective, the fact it meets people's needs is beyond the point. :-)

The fundamental problem there is that it is difficult for academics to come up with original research surrounding relational databases. This may make them a poor area of study for academics, but implies nothing about their usefulness in practice.

Contributors: Martin Marques martin at bugs.unl.edu.ar, Tom Lane tgl at sss.pgh.pa.us, Bruce Momjian pgman at candle.pha.pa.us, Christopher Browne cbbrowne at libertyrms.info

Informix Current News 20-Dec-2003

I read today with great relief about the guilty plea from Phillip White, former CEO of Informix. He has pleaded guilty for securities fraud in 1997 for Informix.

Informix took the lead as having one of the first corporate book-cooking scandals in these past years. The behavior is abhorrent. The fall out affected both companies' and individuals' net worth significantly. And more importantly, I believe, it began the decay of faith in the high tech industry in the United States.

Our open source community does not necessarily prevent this sort of disaster in the future. However, the mutual altruism and good will that abounds in our community (even with assorted trolls, cliques and flamewars) is inspiring. It is inspiring to those of us who love our work, believe in our products and revel in the elegance of innovative and useful creation.

As PostgreSQL spreads more and more into the mainstream let us not forget, however, our own worth and the higher values we maintain.

Editor: elein at varlena.com