[SQL] Postgres 8.1 sequences and 'CALL'-syntax
Hello,
I use postgres 8.1 and trie to run jboss over sequoia-ha
(http://sequoia.continuent.org/HomePage). But i have an problem with sequences.
Sequoia claims to support for good reasons and db-independece only
"sql-standard(s)". Therefore they DON'T support the postgres-specific "select
nextval('seq_name')". Instead they gave me the hint to use the sql-conform
"call nexval('seq_name')".
But unfortunately i always get only an syntax error on "call" from jdbc-driver
or specially postgres as result.
How can i use sequences in conjunction with "call"-syntax??
Thx in advance
__
Sebastian Schnabl
Qualitype AG
Quality Assurance Systems |Bioinformatics
Moritzburger Weg 67 | 01109 Dresden
fon +49.351.8838 0 | fax +49.351.8838 2809
http://www.qualitype.de
__
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] LinkedList
Ray, There's a good introductory article on Sitepoint for doing this kind of thing: http://www.sitepoint.com/article/hierarchical-data-database The "Modified Preorder Tree Traversal" is quite a nice method that I would expect to be a magnitude faster than recursive joins, although does have some drawbacks. Kind Regards, Neil. On 4/27/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > decibel=# select * from t; > a | b > ---+--- > 1 | 0 > 3 | 1 > 5 | 3 > 7 | 5 > 2 | 0 > 4 | 2 > 6 | 4 > 8 | 6 > (8 rows) > > decibel=# select * from t x join t y on(x.a=y.b) where y.a=7; > a | b | a | b > ---+---+---+--- > 5 | 3 | 7 | 5 > (1 row) > > decibel=# select * from t x join t y on(x.a=y.b) where y.a=8; > a | b | a | b > ---+---+---+--- > 6 | 4 | 8 | 6 > (1 row) > > decibel=# > > As you can see, it selects the right data, but you'll need to step > through it somehow. You might be able to do it with a generate_series(), > or you can use a function. If we get WITH support/recursion in 8.2 you'd > use that. > > I think that "SQL For Smarties" by Joe Celko might have an example of > how to do this without using a function. Even if it doesn't it's a book > any serious database developer should own. > > On Wed, Apr 26, 2006 at 10:35:15AM -0700, Ray Madigan wrote: > > Scott, > > > > Thanks for your reply, I tried what you said, worked around a few things > > but I am still stuck. The main reason is I didn't do an adequate job of > > explaining the situation. The table implements many linked lists and I want > > to traverse one of them given the end of the list. > > > > Say the table contains > > > > h | v | j > > 1 0 100 > > 3 1 300 > > 5 3 500 > > 7 5 700 > > > > 2 0 200 > > 4 2 400 > > 6 4 600 > > 8 6 800 > > > > If I specify t.h = 8 I want to traverse the even part of the table > > If I specify t.h = 7 I want to traverse the odd part of the table > > > > If you can send me to a book to read I am willing > > > > Thanks > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] Behalf Of Scott Marlowe > > Sent: Wednesday, April 26, 2006 8:59 AM > > To: Ray Madigan > > Cc: pgsql-sql@postgresql.org > > Subject: Re: [SQL] LinkedList > > > > > > On Wed, 2006-04-26 at 11:09, Ray Madigan wrote: > > > I have a table that I created that implements a linked list. I am not an > > > expert SQL developer and was wondering if there are known ways to traverse > > > the linked lists. Any information that can point me in the direction to > > > figure this out would be appreciated. The table contains many linked > > lists > > > based upon the head of the list and I need to extract all of the nodes > > that > > > make up a list. The lists are simple with a item and a link to the > > history > > > item so it goes kind of like: > > > > > > 1, 0 > > > 3, 1 > > > 7, 3 > > > 9, 7 > > > ... > > > > > > Any suggestions would be helpful, or I will have to implement the table > > > differently. > > > > You should be able to do this with a fairly simple self-join... > > > > select a.id, b.aid, a.field1, b.field1 > > from mytable a > > join mytable b > > on (a.id=b.aid) > > > > Or something like that. > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > > > ---(end of broadcast)--- > > TIP 6: explain analyze is your friend > > > > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Migrating a Database to a new tablespace
Hi, Tom, Tom Lane wrote: >>As I said the leftovers are likely to be caused by hard kills and >>backend crashes, so I would not go into deeper analysis, but maybe the >>finding and possibly removing of such leftovers should be half-automated >>to assist server admins. > > It's been discussed. Personally I'm afraid of the idea of automatically > deleting files that seem unreferenced, but having a tool to find them > for manual deletion isn't a bad idea. I also don't like the idea of full automated deletion, that's why I wrote half-automated. I thought of a tool that enumarates suspective files. Then admins can look at the mtime/atime, or move them away and try what happens, or even pipe the output to "xargs rm -rf" if they want. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Migrating a Database to a new tablespace
Hi, Bruce, Bruce Momjian wrote: > I have work someone did in the past. I just need to be updated to deal > with tablespaces. > > ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* > > Let me know if you want details. It looks nice, but I would not like automatic deletion (or only optional or configurable), there might be valid reasons for "alien" files to exist. (README files for admins to communicate what this directory is used for, MacOS ressource forks, Reiser4 special files etc...) What about putting the identification code in a set returning function or view that can be called by the admin. Then tools like nagios can call it and ring the alarm bells when unknown files appear, or possibly autovacuum calls it once on each session to generate log warnings. Btw, are CLUSTER and ALTER TABLE ALTER COLUMN statements that "create or drop a relation" in this sense? And what is with tables getting extended to more than one file? Another idea would be to first create and sync log entries about the files to be created / deleted, and then actually create / delete the files. So on log replay, the backend could watch out for file operations from transactions that never completed. It will slow those operations down, but such file operations are both seldom and relatively expensive on most filesystems. And it does not help in case of fsync=off, but those admins already take worse risks than wasting disk space. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: 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
[SQL] any one can help me how to get multiple rows in postgresql using arrays in functions
any one can help me how to get multiple rows in postgresql using arrays in functions Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-4133-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9886774209|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records.
Re: [SQL] any one can help me how to get multiple rows in postgresql using arrays in functions
On Thu, Apr 27, 2006 at 04:57:26PM +0530, Penchalaiah P. wrote: > any one can help me how to get multiple rows in postgresql using arrays > in functions What is it you are trying to do? A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Migrating a Database to a new tablespace
Markus Schaber <[EMAIL PROTECTED]> writes: > Bruce Momjian wrote: >> I have work someone did in the past. I just need to be updated to deal >> with tablespaces. >> >> ftp://candle.pha.pa.us/pub/postgresql/mypatches/checkfile.* > It looks nice, but I would not like automatic deletion (or only optional > or configurable), there might be valid reasons for "alien" files to > exist. (README files for admins to communicate what this directory is > used for, MacOS ressource forks, Reiser4 special files etc...) > What about putting the identification code in a set returning function > or view that can be called by the admin. Yeah, one thing I never liked about the previously proposed patches was that they were tied to postmaster start/restart. It'd be better to have an external tool that could be invoked on demand. Maybe something close to oid2name. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] INSERTing values from usertypes how-to?
Comming back after SELECTing into usertypes, is there a nice way to insert data into table from usertype, in a nice way? For instance, I have table like this: CREATE TABLE tblA ( id int4, key varchar, value varchar ) Then, in my function, I do this: CREATE FUNCTION testInsert() RETURNS void AS $BODY$ DECLARE insert_tblA tblA; insert_tblA.id = get_next_id_or_something(); insert_tblA.key = get_user_key_or_something(); insert_tblA.value = get_some_value(); INSERT INTO tblA ( id, key, value) VALUES ( insert_tblA.id, insert_tblA.key, insert_tblA.value ); END $BODY$ LANGUAGE 'plpgsql'; Now, in this particular example it seems stupid first to populate usertype and then insert the data from it to the tblA table, but in my actuall function that makes sense (the function is quite large for posting here). Now, since sometimes I have 30 columns in a table, I tried to find a way to simply 'pour' the usertype into the table. I tried something like: INSERT INTO tblA (insert_tblaA); but postgres complains (and I guess I expeted that) with an error saying that tblA.id is of type int4, and I'm trying to insert value of type tblA. So, is there a way to map all those type-members in a way so that one could easily INSERT those into table? Mario ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] any one can help me how to get multiple rows in postgresql
Penchalaiah P. wrote: any one can help me how to get multiple rows in postgresql using arrays in functions What exactly do you want to do? Are you trying to match a column against multiple values in an array, or what? What difference is the function making to your problem? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] INSERTing values from usertypes how-to?
Mario Splivalo <[EMAIL PROTECTED]> writes: > Comming back after SELECTing into usertypes, is there a nice way to > insert data into table from usertype, in a nice way? Try insert into tblA values (insert_tblA.*); This works in CVS HEAD at least, not sure how far back. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Porting application with rules and triggers from PG 7.4.x
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!
Sorry for the late response, but I'm quite busy and I wanted
to test this before replying...
Bruce Momjian schrieb:
> Tom Lane wrote:
>> Andreas Haumer <[EMAIL PROTECTED]> writes:
>>> How can I get the functionality of an "deferred AFTER trigger"
>>> again with PostgreSQL 8?
>> Use CREATE CONSTRAINT TRIGGER. The manual is fairly negative about this
>> but I don't actually foresee it going away any time soon.
>
With PostgreSQL 8.x, CREATE CONSTRAINT TRIGGER indeed seems the way to go.
I have now converted all my triggers used for temporal table
referential integrity checking from (example)
CREATE TRIGGER trigger_fk_ug_pns
AFTER UPDATE OR DELETE
ON mwdb.t_ug
FOR EACH ROW
EXECUTE PROCEDURE mwdb.func_fk_temporal_trigger('t_pns', 'ug', 't_ug', 'id');
(as used with PostgreSQL 7.x)
to (example)
CREATE CONSTRAINT TRIGGER trigger_fk_ug_pns
AFTER UPDATE OR DELETE
ON mwdb.t_ug
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE mwdb.func_fk_temporal_trigger('t_pns', 'ug', 't_ug', 'id');
(for PostgreSQL 8.x)
The documentation on "CREATE CONSTRAINT TRIGGER" really is not
very extensive, so I cannot say if this really is the right way
to use it, but it seems to work as far as I have tested. I still
do not have a very confident feeling, though...
> Do we need to update the manual?
>
Yes, please!
If CREATE CONSTRAINT TRIGGER is the only way to solve this
particular problem, it would be very good to have a more
elaborate documentation about it. Currently the documentation
mostly says "not intended for general use". Some real world
examples in chapter 33 ("Triggers") would be nice, too
(If you want you can even use the example from my previous
post, which seems to be a typical application for deferred
triggers... :-)
Thank you all!
- - andreas
- --
Andreas Haumer | mailto:[EMAIL PROTECTED]
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFEUQjOxJmyeGcXPhERAl32AJ9n7SJgudu5AhdT0AX98FdalyrTowCfTojE
fD+uh7lID845Hi6TPqe/3rM=
=3Pu4
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] LinkedList
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. Any information that can point me in the direction to figure this out would be appreciated. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: It may not be exactly suitable, but this one does only traversal (assuming the list is not clsoed) create table linkedlist(prevnode int, nextnode int, val int); -- HEAD insert into linkedlist values(null,1,0); insert into linkedlist values(1,2,10); insert into linkedlist values(2,3,20); insert into linkedlist values(3,4,30); insert into linkedlist values(4,5,40); -- TAIL insert into linkedlist values(5,null,50); -- TRAVERSE begin; declare mc cursor for select * from linkedlist order by nextnode; fetch 1 from mc; fetch 1 from mc; ... close mc; commit; which is nothing more than, select * from linkedlist order by nextnode; Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
