|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
In discussing the semantics of array operations with regards to the SQL99 specification, the question was raised whether arrays are "an anathema to all we know and love" in relational databases. Of course the array semantics are defined in the SQL99 specifications... PostgreSQL is an ORDBMS, not just an RDBMS. A column holds a type of value. Any kind. The structure and operands define the type. The data defines the value. This holds true for simple types like an integer or complex types like an array. The database data is relatively "type blind" in an ORDBMS. It uses the standard overloaded operands to determine the type of function to perform for all of the usual RDBMS utilities. Constraints, triggers, sorting, etc. all apply. That's what the ORDBMS stuff can give you. Arrays are a natural extension. Arrays don't necessarily imply denormalization. Of course you can denormalize via arrays, but it tends to make things harder for you. And I believe the same thing is true for denormalized integer columns. This begs the question of when and why would one use arrays in a relational model. This may be difficult to see because arrays are an abstract data type. A specific example would be clearer. For example, a definition of a polygon is an array of Points. Points, themselves are an array. The particular polygon may have a unique key for normalized inclusion as a foreign key, but the data is still in array form. The data is only useful as a complete array. The actual postgreSQL implementation of polygons and points doesn't use the newer cleaner array abstraction, I think. But if I were reimplementing them, I would build on top of the new array capabilities. The point is to show an array structured object which makes sense in context.
DBI is a database independent client side perl interface for PostgreSQL. Because it is perl, it uses perldoc rather than man. To see the documentation use $ perldoc DBI The documentation is quite long but it gives you key information on the basics. One question involved with this thread was how to connection across a network. To do this, create a PostgreSQL connection string and use it as a parameter to DBI->connect() A connection string looks like this: "dbi:Pg:dbname=db;host=machine;port=5432" use DBI; my $dbh = DBI->connect( "dbi:Pg:dbname=db;host=db_server;port=5432", "test", "test", {RaiseError => 1} ) or die "Unable to connect: $DBI::errstr\n"; $dbh->disconnect;
The specification of the timing of constraint checks is defined when you create a table and add a constraint. You may not specify the timing for all constraints--only for foreign key constraints. The constraint is checked according to the deferment options: either immediately or possibly at the end of the transaction. When you are loading data that is not in dependency order, for example, you may want to do so in a transaction and have the constraints deferred. The deferment options on foreign key constraints are: [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]Reformatted from 73/doc/html/sql-createtable.html DEFERRABLE or NOT DEFERRABLE controls whether the constraint can be deferred.
Vadim Mikheev is leaving the PostgreSQL Core Development Team. For some time now Vadim Mikheev has not had time to be a major contributor to PostgreSQL and seems to feel this is the time to acknowledge this formally. Vadim was one of the initial Core Team which took over ownership of Postgres95 when it was turned over by UCBerkeley and Andrew Yu and Jolly Chen. Many people have given great thanks to Vadim for his excellent work. The majority of authors of the public responses to Vadim's message are all notable themselves which says much about Vadim's work. D'Arcy Cain: I know that a lot of what makes PostgreSQL what it is today is can be directly attributed to your work. Thank you. And thank you for the personal help back when I was working on the PostgreSQL trigger documentation.Mike Mascari: It's hard to imagine PostgreSQL with out MVCC, WAL, subselects, etc.Hiroshi Inoue: If you had not been here in the PostgreSQL community, I might not have been here.Marc Fournier: ...unlike most co-workesr, I can definitely say its been a great pleasure to have known, and worked, with you ... you brought, and gave, alot to the project, and for that, we thank you ... thanks :)Bruce Momjian: Vadim, sorry to see you go. You baled me out of a lot of difficult problems. Thanks.Jan Wieck: Do swidanie i bolshoi sbaseebo, Vadim.Andrew Sullivan I can say for sure that if it weren't for your work, we'da been sunk. Robert Treat summarizes the general feeling with a quote from Isaac Newton: "If I have seen farther than others, it is by standing on the shoulders of giants", Issac Newton There is a proposal to add a new Hall of Fame page to the PostgreSQL site to honor both Vadim and Thomas Lockhart.
There are many ways to extract the code source which defines a function. It does require going through the system catalogs to do so, however. And the output is a little messy. But it certainly is helpful while you are debugging to know what version you have currently defined. I use this little view. It combines the entire function definition into one element so that, for example, I can see all of the functions which operate on or return a type by qualifying on the function_definition. I've thrown in some newlines so the pl source can actually be read from psql, though it is not necessarily pretty. create or replace view showfunctions as select l.lanname, t.typname || ' = ' || n.nspname || '.' || p.proname || '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')' as function_definition, (select case when lanname <> 'c' then '\n\n'||prosrc ||'\n\n' else '\n'||prosrc ||'.so\n\n' end) as source from pg_proc p, pg_type t, pg_namespace n, pg_language l where p.prorettype = t.oid and p.pronamespace = n.oid and p.prolang = l.oid; -- shows everything in the public schema (and any function with -- public in its name) select * from showfunctions where function_definition like '%public%'; -- shows all functions which take or return boolean values select * from showfunctions where function_definition like '%boolean%'; The first column of the output indicates the language. The language is 'c', 'internal', 'sql' or one of the pl languages you have defined in your database. For example, 'plpgsql' and 'plperl'.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in! Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009 |