[SQL] Re: Recusrive Functions in 7.0.3
mark proctor <[EMAIL PROTECTED]> writes: > However if I put the PERFORM line back in to create a recursive > function it just goes on forever, and I only have 6 nodes. Hm. There may be a bug here, or maybe you're still confused... but I'm not eager to reverse-engineer your table declarations and data from this sketch. Would you mind providing a complete example, ie a SQL script to reproduce the problem starting from an empty database? regards, tom lane
[SQL] How to make operator class?
when create indexes, use operator class like this. CREATE INDEX idx_name ON tbl_name (col_name [operator class]); so, i made an operator. but i think that it is different operator class because operator class doesn't work at all. then, how to make operator class? == == ¿ì¸® ÀÎÅͳÝ, Daum Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀÏ³Ý Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL http://www.daum.net
Re: [SQL] fetching the id of a new row
On Thu, Feb 08, 2001 at 05:28:59PM -0500, Jelle Ouwerkerk wrote: > Hi, > > How might I insert a new row into a table and return the id of the new row > all in the same SQL statement? The id is generated by a sequence. Up to > now I've been getting the nextval of the sequence first and then inserting > with the id in a second SQL exec. Is there a faster way (in a general > case, without writing SQL or plpgsql functions)? > > Thanks I do not know of a way to insert and select in one statement without the use of a function (what's the problem with those, by the way?), but as far as I can tell nextval() will return the next value for any backend, so if you have more than one backend inserting at the same time you might end up inserting with the same id twice. Instead you should insert once, without specifying the id (so that the default value, which must be set to nextval()) will be used; to obtain the id, if indeed you need it, you can than select currval(), which is guaranteed to work on a per-backend basis. Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
Re: [SQL] How to make operator class?
"Kim Yunhan" <[EMAIL PROTECTED]> writes: > then, how to make operator class? See the Programmer's Guide, particularly http://www.postgresql.org/devel-corner/docs/postgres/xindex.htm If this is a followup to your previous question about making a reverse-order index, you'll need to start by making a support function that delivers the reverse (negative) of what the existing btree comparison function for the datatype does (see btint4cmp and friends). The remaining operators to comprise the opclass are the same ones that exist already, you just need to swap their positions (use '<' for '>' etc). Good luck! regards, tom lane
Re: [SQL] How to make operator class?
Tom, > > Hmmm thinking about it logically, shouldn't the database engine be > > able to use an index on a single column either backwards or forwards? > > Sure, but he wanted a sort with the first column in ascending order and > the second in descending order. You can't get that by scanning an index > that has both columns in the same order... Aha. I see the problem. My approach would be to use two seperate indexes, but maybe that's not sophisticated enough :-) And, Tom, you're working too hard again. It's Saturday. Go home. -Josh Berkus (Who is home, just VPN'ing into my office). -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
[SQL] 16 Argument Limit ?!?!?
Tom, Jan, After spending an entire day writing and debugging a particularly complex PLPGSQL function, I just received this error: Procedures Cannot Take More than 16 Arguments. This is a truly devastating blow to my application (which is already behind schedule, needless to say). I've been using Functions to control all mods and additions to the program's data tables. I guess I just haven't had to wirte a function for a table with more than 16 columns yet. The current function uses 36 parameters, 3 of which are arrays. Is there any way around this? Maybe something using the RECORD data type? Can anyone help me with an explanation of the RECORD data type and how to get data into and out of it? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco
