[SQL] Trick to 'run' a view on two databases and combine the result ?
Hello, I have a 'big problem' : I have to show some data from two identical databases so I need to run a querry (view, ..etc) on both databases and show the united result ... Any ideea how to obtain this result ? Thank You, Adrian Din -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Column with recycled sequence value
> -Original Message- > From: Andrew Sullivan [mailto:[EMAIL PROTECTED] > Sent: Donnerstag, 13. Jänner 2005 20:49 > To: 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] Column with recycled sequence value > ... > > used 2^32 will be reached soon and then? There are far less > than 4G-records > > saved thus these values may be reused. How can this be accomplished? > > You can set the sequence up to cycle (so once it gets to the end, it > wraps around to the beginning again). The keyword is CYCLE at CREATE > SEQUENCE time. It defaults to NO CYCLE. > > One potential problem, of course, are collisions on the table, > because some value wasn't cleared out. It sounds like you don't have > that problem though. actually I am asking just because of that. The thing is, that I am able to synthetisize wonderful SELECTs but I have no unterstanding of how to first find out wether a record doesn't exist in order to create it in the same transaction. As user of procedural languages I'm thinking of variables and so on. But AFAIK SQL has a way to also find a way without them. OR I'd like to read that I can't use SQL for this purpose and at minimum PL/pgSQL would be appropriate for me. PS. 32-Bit is my bussiness limitation - but also that is thinkable. thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Trick to 'run' a view on two databases and combine the result ?
On Fri, Jan 14, 2005 at 11:52:46AM +0200, Din Adrian wrote: > I have to show some data from two identical databases so I need to run a > querry (view, ..etc) on both databases and show the united result ... If you're writing an application then you could make two database connections, run the query in each, and merge the results in the application. If you want to do it in PostgreSQL then you could use contrib/dblink. If that's not helpful then please provide more information about what you need to do. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Column with recycled sequence value (solved (somehow))
Thank you all for your thinkings. It seems like there's no realy good way to solve that kind of problem (maybe used IDs). Thus I made a design change happen. We went to 64-Bit which should solve the problem until the computers already fellt into dust. > -Original Message- > From: PFC [mailto:[EMAIL PROTECTED] > Sent: Freitag, 14. Jänner 2005 02:27 > To: Andrew Sullivan; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] Column with recycled sequence value > > > > You could update all the fields which use this sequence > number. You say > you have a lot of activity so you must have mahy holes in > your sequence, > probably of the possible 2^32 values, only a fes millions are used. > > You can do the following : > > - Take down the database, back it up, and restart it > with a single user, > so only you can connect, using psql. > - Create a table : > CREATE TABLE translate ( new_id SERIAL PRIMARY KEY, old_id INTEGER, > UNIQUE(old_id) ) WITHOUT OIDS; > > - Insert into this table all the used sequence values > you have in your > database. If you have all the proper constraints, these > should come from > only one table, so it should be straightformard : > > INSERT INTO translate (old_id) SELECT id FROM your_table; > > Thus the "translate" table maps old id's to a new > sequence that you just > started, and that means your new id's will be compactly > arranged, starting > at 1. > > - Update your existing table, joining it to the > translate table, to > replace the old id by the new id. > > > > On Thu, Jan 13, 2005 at 06:08:20PM +0100, KÖPFERL Robert wrote: > >> Hi, > >> > >> suppose I have a let's say heavy used table. There's a > column containing > >> UNIQUE in4 > >> values. The data type musn't exceed 32-Bit. Since however > the table is > >> heavy > >> used 2^32 will be reached soon and then? There are far less than > >> 4G-records > >> saved thus these values may be reused. How can this be > accomplished? > > > > You can set the sequence up to cycle (so once it gets to the end, it > > wraps around to the beginning again). The keyword is CYCLE > at CREATE > > SEQUENCE time. It defaults to NO CYCLE. > > > > One potential problem, of course, are collisions on the table, > > because some value wasn't cleared out. It sounds like you > don't have > > that problem though. > > > > A > > > > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] assign the row count of a query to a variable
Hi, I'm trying to assign the row count of a query to a variable in a function but I'm not having any luck. Could someone tell me the syntax? I've been looking in the docs and googling for a long time but just can't find the answer. I've tried: CREATE OR REPLACE FUNCTION ret1() RETURNS int4 AS ' BEGIN declare var int4; begin --select var count(*) from T; --select var (count(*)) from T; --select var = count(*) from T; var = select count(*) from T; return var; END; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] assign the row count of a query to a variable
var := count(*) from T; or : SELECT INTO var count(*) from T; ---(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: [SQL] assign the row count of a query to a variable
On Fri, Jan 14, 2005 at 03:43:04PM -0500, Kevin B. wrote: > I'm trying to assign the row count of a query to a variable in a function > but I'm not having any luck. Please be more specific than "not having any luck." What are you expecting to happen and what actually does happen? > Could someone tell me the syntax? I've been looking in the docs and > googling for a long time but just can't find the answer. See the "Basic Statements" and "Expressions" sections of the PL/pgSQL documentation. Either of the following should work: var := count(*) FROM T; SELECT INTO var count(*) FROM T; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SQL Query Performance tips
Hi All, I have a partial table structure as below tbl_user_main user_id pk user_level references tbl_level user_class references tbl_class user_level references tbl_level tbl_level level_id pk level_name level_points tbl_weapon weapon_id pk weapon_name weapon_level references tbl_level weapon_class references tbl_class weapon_alignment references tbl_alignment weapon_cost tbl_class class_id pk class_name tbl_alignment alignment_id pk alignment_name tbl_user_weapon user_id references tbl_user weapon_id references tbl_weapon I want to know how many weapons a given user has of a particular class or alignment where the level of the weapons are equal or less than the players own level. For this I have developed the following SQL: 1 | select alignment.alignment as alignment, count(distinct(weapon.weapon_name)) as count from 2 | ( select * from tbl_alignment where alignment_id != 1 ) alignment, 3 | ( select * from tbl_weapon) weapon, 4 | ( select * from tbl_user_main where user_id = $user_id ) person 5 | where weapon.weapon_id IN ( 6 | select u.weapon_id 7 | from tbl_weapon u 8 | where u.weapon_level <= person.user_level 9 | and u.cost = 0 10| or u.weapon_id IN ( 11| select uu.weapon_id 12| from tbl_user_weapon uu 13| where uu.user_id = person.user_id 14| ) 15| ) 16| and alignment.alignment_id = weapon.weapon_alignment 17| group by alignment.alignment 18| order by alignment.alignment ASC; To clarify lines 5 through 15 - a weapon can be free or cost some amount. To track users that have bought a weapon there is the tbl_user_weapon table. Every purchase gets listed in there. The count must take into account all free weapons and weapons which the user has purchased. At the moment there are some 300 weapons. In the case above the "alignment" with id 1 is a catchall so I disregard it. $user_id can be any user_id from tbl_user_main. The result of a query such as this is along the lines of; alignment | count --+--- Shadow| 4 Heavenly | 6 This takes a long time to complete - circa 3 seconds. Which is fine when run one off - but it appears in a section of a website that will potentially be accessed a lot and I can see it causing a few issues in terms of table/row locking as the game app uses these tables a lot. ideally I need it to run a lot quicker. Can anyone see any ways to speed this up? I have considered views but these seem to just be a way of aliasing a query as opposed to the materialised views present in other RDBMS's. Short of creating another 3rd form table that has user_id | alignment_id | count Which would get updated upon each weapon purchase I cannot see a low overhead way of getting the data. Creating a table such as this would need to be a last resort as its maintenance will quickly become a headache due to the number of purchase routes in the project. Any help is much appreciated. Thanks, -- Michael Ossareh (M²) Technical Manager 12snap UK Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] return value of the trigger function
Hi I have big problem with a trigger function. Defs: CREATE TABLE foo (id int2, name varchar(20)); foo.id cannot be serial or autoint because it will not be unique. Now I need a trigger that return foo.id of the inserted record. INSERT INTO foo VALUES (max(foo.id)+1,'junk'); IMHO the trigger should be: CREATE TRIGGER tr_get_new_id AFTER INSERT on foo ON EACH ROW EXECUTE PROCEDURE get_new_id(); But I cannot create the function :( I don't know what type should be input and how about output? trigger or int2? TIA, -- Jarek Pudelko JP272-RIPE ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] TEXT type blob extraction in ecpg
Does any one know how to properly extract a TEXT type blob (undefined char length) in ecpg (Linux system)? We are converting our SQL from Informix to PostGres. Informix had a specific struct defined and I believe handled the dynamic memory allocation, but I cannot find any guidance on this issue for PostGres. Anyone have any suggestions? Thanks, Tom ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] I am writing a MS SQL server conversion utility and am having an issue with timestamp
Any one have a good idea for dealing with a timestamp where only time is available on some of the source records? Some records have both time and day. My MSSQL database has 290 tables so splitting the fields would be a very large project. Is there a way to add just the time part of date time to timestamp? Joel Fradkin Wazagua, LLC 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, LLC. All rights reserved. WAZAGUA, LLC This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
