Re: [PATCHES] psql \i handling ~ in specified file name
Zach Irmen wrote: > refers to following item on TODO > Have psql '\i ~/' actually load files it displays from home > dir This expansion should also apply to all other commands that take file names. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Quoting of psql \d output
Tom Lane wrote: > I think if we change it here we will also have to revisit hundreds of > places in the backend, such as this one: > regression=# select * from public.bar; > ERROR: relation "public.bar" does not exist > and indeed the whole question of what we are using quotes for in > messages becomes open again. I remember someone once posted Oracle's message style guidelines, and they actually specify that you are not supposed to write 'foo.bar' in messages, but you are supposed to write 'schema foo, table bar' or some permutation. Personally, I like this rule, but it seems prohibitively hard and/or cumbersome to implement it everywhere in an i18n-safe way. But we might want to use it when it's easily possible. The alternative is using the dotted notation, and in that case we should use SQL quotation rules because that is the only way to be internally consistent. (Smart quotation or full quotation is another matter.) In that case the generated string falls under the "already supplies its own quotes" rule and the outer format string should not put the %s in quotes again. (Yes, that means that psql should be changed somehow.) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Some Documentation Changes
1. In keeping with the recent discussion that there should be more said about views, stored procedures, and triggers, in the tutorial, I have added a bit of verbiage to that end. 2. Some formatting changes to the datetime discussion, as well as addition of a citation of a relevant book on calendars. Index: advanced.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/advanced.sgml,v retrieving revision 1.38 diff -c -u -r1.38 advanced.sgml --- advanced.sgml 29 Nov 2003 19:51:36 - 1.38 +++ advanced.sgml 30 Dec 2003 01:58:24 - @@ -65,10 +65,24 @@ Views can be used in almost any place a real table can be used. -Building views upon other views is not uncommon. +Building views upon other views is not uncommon. You may cut down +on the difficulty of building complex queries by constructing them +in smaller, easier-to-verify pieces, using views. Views may be +used to reveal specific table columns to users that legitimately +need access to some of the data, but who shouldn't be able to look +at the whole table. - + +Views differ from real tables in that they are +not, by default, updatable. If they join together several tables, +it may be troublesome to update certain columns since the +real update that must take place requires +identifying the relevant rows in the source tables. This is +discussed further in . + + + Foreign Keys @@ -387,6 +401,169 @@ + +Stored Procedures + + + stored procedures + + +Stored procedures are code that runs inside the database + system. Numerous languages may be used to implement functions and + procedures; most built-in code is implemented in C. The + basic loadable procedural language for + PostgreSQL is . + Numerous other languages may also be used, including , , and . + + +There are several ways that stored procedures are really + helpful: + + + +To centralize data validation code into the + database + +Your system may use client software written in several + languages, perhaps with a web application + implemented in PHP, a server application implemented + in Java, and a report writer implemented in Perl. + In the absence of stored procedures, you will likely find that data + validation code must be implemented multiple times, in multiple + languages, once for each application. + +By implementing data validation in stored procedures, + running in the database, it can behave uniformly for all these + systems, and you do not need to worry about synchronizing + validation procedures across the languages. + + + +Reducing round trips between client and server + + + A stored procedure may submit multiple queries, looking up + information and adding in links to additional tables. This takes + place without requiring that the client submit multiple queries, + and without requiring any added network traffic. + + +As a matter of course, the queries share a single + transaction context, and there may also be savings in the + evaluation of query plans, that will be similar between invocations + of a given stored procedure. + +To simplify queries. + +For instance, if you are commonly checking the TLD on domain + names, you might create a stored procedure for this purpose, and so + be able to use queries such as select domain, tld(domain) + from domains; instead of having to put verbose code + using substr() into each query. + + +It is particularly convenient to use scripting languages + like Perl, Tcl, and Python to grovel through strings + since they are designed for text processing. + +The binding to the R statistical language allows + implementing complex statistical queries inside the database, + instead of having to draw the data out. + + +Increasing the level of abstraction + +If data is accessed exclusively through stored procedures, + then the structures of tables may be changed without there needing + to be any visible change in the API used by programmers. In some + systems, users are only allowed access to + stored procedures to update data, and cannot do direct updates to + tables. + + + + + + + +These benefits build on one another: careful use of stored + procedures can simultaneously improve reliability and performance, + whilst simplifying database access code and improving portability + across client platforms and languages. For instance, consider that + a stored procedure can cheaply query tables in the database to + validate the correctness of data provided as input. + +Instead of requiring a whole series of queries to create an + object, and to look up parent/subsidiary objects to link it to, a + stored procedure can do all of this efficiently in the database
Re: [PATCHES] [COMMITTERS] pgsql-server/ oc/src/sgml/datatype.sgml
On Sat, 20 Dec 2003, Bruce Momjian wrote: >Tom Lane wrote: >>This is a horrid, horrid idea. Datestyle is already a complete mess >> ... >>Please revert that part of the patch and instead invent a new GUC >>variable that's specifically for interval formatting. > > OK, I have backed out the patch. [...] Short summary... Before I try this, through email someone suggested yet a different idea... Would formatting functions for intervals such as... to_iso8601basic_char(interval) -- return ISO-8601 basic fmt interval to_iso8601basic_char(timestamp) -- return ISO-8601 basic fmt date/time would be better than a new GUC variable? Longer... Tom Lane wrote: > > This is a horrid, horrid idea. Datestyle is already a complete mess > because it is being used to control several things; it should have > been two or possibly three GUC variables not one. Sticking in yet > another behavior is just not acceptable IMHO, especially when it's > defined as non-orthogonally as that. > > Please revert that part of the patch and instead invent a new GUC > variable that's specifically for interval formatting. First I just wanted to say how it ended up using datestyle... In the earlier discussion when Andrew asked about a way of outputting ISO-8601 Basic Format time intervals, the use of datestyle came up, and noone objected to the use of datestyle at that point. ... Tom was suggesting: http://archives.postgresql.org/pgsql-patches/2003-09/msg00122.php TL> TL> Perhaps call it "compact" or "terse" datestyle? ... and Peter suggested: http://archives.postgresql.org/pgsql-patches/2003-09/msg00129.php PE> PE> iso8601 PE> PE> Keep in mind that SQL itself is also a kind of ISO, so being PE> more specific is useful. Regarding the non-orthogonality, I was suspecting that most applications that use ISO-8601 Basic Formats would use them consistently for dates (19990131) and intervals (P1Y1M). But I do see your point and agree this isn't a good solution. If the developers would like separate GUC variables for formatting dates vs intervals, I would be happy to do so. On the other hand, if the idea of outputting ISO-8601 intervals is likely to be rejected anyway, I'd be happy to not do it too. :-) Or, how would people feel instead about formatting functions to produce the various ISO-8601 formats? to_iso8601basic_char(timestamp) to_iso8601basic_char(interval) I think this could be especially useful since the docs: http://developer.postgresql.org/docs/postgres/functions-formatting.html say that "to_char(interval, text)" is being deprecated, meaning that converting intervals to formats other systems accept will soon become harder. Personally, though, I'm most interested in the input side. I have an application that uses ISO-8601 Basic Format for all it's time information (Dates, Times, and Intervals), and wanted to load this information into PostgreSQL. I was happy to see that Dates and Times loaded. Unfortunately intervals did not. A quick investigation showed that PostgreSQL currently has an undocumented shorthand is similar but frustratingly different from ISO-8601: (i.e. '1Y1M'::interval means '1 year 1 minute' to PostgreSQL 7.3X, while 'P1Y1M' means '1 year 1 month' to ISO-8601). Even if nothing is done to the output side, allowing inputting of such intervals would benefit me. Would the developers prefer a patch allowing the inputting of such intervals, and not support outputting at all? > BTW, I can tell without looking that the patch is deficient in > documentation; if it has effects on GUC variables, why is there no > mod in runtime.sgml? Point well taken. Before I submit any future patches I will try to be more careful in this regard. Ron PS: The spec I'm referring to is ISO-8601... Section 5.5.4.2 http://www.webaugur.com/bibliotheca/standards/iso8601/154N362/index-25.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [COMMITTERS] pgsql-server/ oc/src/sgml/datatype.sgml
Ron Mayer <[EMAIL PROTECTED]> writes: > Would formatting functions for intervals such as... >to_iso8601basic_char(interval) -- return ISO-8601 basic fmt interval >to_iso8601basic_char(timestamp) -- return ISO-8601 basic fmt date/time > would be better than a new GUC variable? I could see arguments for both --- in the timestamp world we have DateStyle to control the default output format, plus to_char when you want something different. I'd suggest continuing with your plan of providing GUC control over the default interval format (just use a separate GUC variable, please, for orthogonality). If you want to add an explicit formatting function later, you can do that too. BTW, I do not recall exactly why Karel wants to deprecate to_char(interval), but I don't think it's because of any fundamental objection to the notion of a formatting function. I think it was because the present definition is badly designed and needs to be replaced with a different API. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
