Re: [SQL] SQL question
Philip Warner <[EMAIL PROTECTED]> writes: > At 02:26 17/07/00 -0400, Tom Lane wrote: >> Well before my time, I guess --- as long as I've been paying attention, >> the function manager's approach was to call the routine first and *then* >> insert a NULL result ... if the routine hadn't crashed first. That's >> about as braindead a choice as I can think of, but that's what it did. > Out of curiosity, what does it do now? As of current sources there is a distinction between "strict" and "non-strict" functions. A strict function must return NULL if any input is NULL, therefore the function manager won't call it at all if there is a NULL input value, but just assume the NULL result. (Some other DBMSes have the same concept under different names, like "NOT NULL CALL".) A non-strict function is assumed to be able to take care of itself. It gets called anyway and must check to see if any of its inputs are NULL, then decide what it wants to do about it. Both strict and nonstrict functions can return NULL if they wish, though I've not seen many cases where a strict function would want to. This is all predicated on a new function call interface that provides an explicit isNull flag for each input, as well as an isNull flag for the function result. The real problem with the old code was that we didn't have that, and there is no non-kluge workaround for not having the information. If I understand Thomas' remarks correctly, at one time in the past the function-call code operated as though all functions were strict. I suppose that foundered on the little problem that certain operations like IS NULL and IS NOT NULL *must* be non-strict. So someone changed it to the opposite convention, but didn't follow through to the bitter end: there has to be an explicit null flag for *each* argument, as well as a clean way for the function to say whether it is returning a null or not. Defaulting to non-strict also created a ton of potential crash sites in routines that couldn't cope with null-pointer inputs. We've been gradually "fixing" those by adding explicit tests for nulls, but it's always been a stopgap solution IMHO. Now there's a better way. 99% of the built-in functions in the backend are "strict" and so will no longer need special checks to defend against null inputs, because they'll never see 'em again. regards, tom lane
Re: [SQL] SQL question
Thomas Lockhart <[EMAIL PROTECTED]> writes: >> The immediate cause of this gripe was discussed just a day or so ago >> on one or another of the pgsql lists. The timestamp-to-date conversion >> routine has this weird idea that it should kick out an error instead >> of returning NULL when presented with a NULL timestamp. That's a bug >> IMHO, and I've already changed the code in current sources. > That's not a bug, that was a feature, sort of. At least when I coded it, > Postgres *refused* to call any routine with NULL input, assuming that > NULL would be returned. Well before my time, I guess --- as long as I've been paying attention, the function manager's approach was to call the routine first and *then* insert a NULL result ... if the routine hadn't crashed first. That's about as braindead a choice as I can think of, but that's what it did. > A clever short-circuit, and the elog(ERROR) in > the conversion routine was just a safety net. Because it was also the > case that any routine returning a NULL pointer crashed the backend. > Now that those things aren't true, we are rewriting history to say that > they were bugs all along, eh? ;) Fixing that one routine to behave that way, when none of the hundreds of others that might see a NULL input do the same, qualifies as a bug IMHO. But it's all water over the dam, now that fmgr has been redesigned. regards, tom lane
RE: [SQL] Select by priority
>> I think the order thing will work though I have no choice of using >> numbering for the address_type as I am working off a pre-existing >> database. They are using a mnemonic char type. I am essentially batch >> downloading and processing this and other database tables for query >> and presentations using web pages. BTW, does the 'limit' feature exist >> in Oracle? The main database I am extracting data from is Oracle... I do not know whether you can add a table associating "priority" with "address_type". If you can, you might try something like the following example, using the address_priority table: create table address_table ( person_idinteger, address_type varchar(1), address varchar(50) ); insert into address_table values ( 1, 'W', 'ROUTE 1, WORK ST'); insert into address_table values ( 2, 'H', 'ROUTE 2, HOME AVE'); insert into address_table values ( 3, 'W', 'ROUTE 3, WORK ST'); insert into address_table values ( 3, 'H', 'ROUTE 3, HOME AVE'); insert into address_table values ( 4, 'M', 'ROUTE 4, MAIL RD'); insert into address_table values ( 5, 'M', 'ROUTE 5, MAIL RD'); insert into address_table values ( 5, 'W', 'ROUTE 5, WORK ST'); insert into address_table values ( 6, 'M', 'ROUTE 6, MAIL RD'); insert into address_table values ( 6, 'H', 'ROUTE 6, HOME AVE'); insert into address_table values ( 7, 'M', 'ROUTE 7, MAIL RD'); insert into address_table values ( 7, 'H', 'ROUTE 7, HOME AVE'); insert into address_table values ( 7, 'W', 'ROUTE 7, WORK ST'); create table address_priority ( address_type varchar(1), priority integer ); insert into address_priority values ( 'M', 1 ); insert into address_priority values ( 'H', 2 ); insert into address_priority values ( 'W', 3 ); select person_id, address from address_table a, address_priority b where (person_id, priority) in (select person_id, min(priority) from address_table a, address_priority b where a.address_type = b.address_type group by person_id) and a.address_type = b.address_type; PERSON_ID ADDRESS -- -- 1 ROUTE 1, WORK ST 2 ROUTE 2, HOME AVE 3 ROUTE 3, HOME AVE 4 ROUTE 4, MAIL RD 5 ROUTE 5, MAIL RD 6 ROUTE 6, MAIL RD 7 ROUTE 7, MAIL RD Appears to work with either Oracle or Postgres (though I changed VARCHAR to VARCHAR2 for Oracle). Gary Farmer
[SQL] Database authentication and configuration
Are there anywhere to configure so that whenever I call 'psql dbname', it'll always prompt for user authentication instead of getting into the database directly? In addition, I'd like to default various database settings when I connect to the database. Where is the place to do such configuration? TIA.
Re: [SQL] Select by priority
"Gary J. Farmer" <[EMAIL PROTECTED]> writes: > >> I think the order thing will work though I have no choice of using > >> numbering for the address_type as I am working off a pre-existing > >> database. They are using a mnemonic char type. I am essentially batch > >> downloading and processing this and other database tables for query > >> and presentations using web pages. BTW, does the 'limit' feature exist > >> in Oracle? The main database I am extracting data from is Oracle... > > I do not know whether you can add a table associating "priority" with > "address_type". If you can, you might try something like the following > example, using the address_priority table: > Appears to work with either Oracle or Postgres (though I changed VARCHAR > to VARCHAR2 for Oracle). > > Gary Farmer That looks like a workable solution. I'll try it out. Thanks... -- Prasanth Kumar [EMAIL PROTECTED]
[SQL] strange error message
Hi all, I'm sure I found somewhere in the docs, that postgreSQL 7.0 accepts ANSI-SQL style join syntax, but when I try: SELECT ta.a, tb.b FROM taba ta INNER JOIN tabb tb ON ta.c = tb.c; I get the strange message: ERROR: transformExpr: does not know how to transform node 501 (internal error) Is my syntax wrong or is this a bug in the parser? I'm using 7.0.0 Gerhard
