Re: [SQL] Just 1 in a series...
Mark Fenbers wrote: What would have to be done if I needed a standard SQL solution? Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Maybe you could t'ry something like : Select whatever from yourtable a where not exists (select * from yourtable b where b.id=a.id and b.timestamp > a.timestamp) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Database query: Notification about change?
Hi, I plan to develop an application that is somewhat like a spreadsheet with cells containing formulas. When a cell value is changed, things must be updated. But the formulas can contain database queries, which means that the cell has to be notified when the database changes in such a way that the result of the query changes. How is this done? I would really like to avoid recalculating the whole thing for each change to the database. I looked in my database book and read about materialized views and triggers. It seems like I should do the following: 1. Make a materialized view from the query. 2. Add a trigger for changes to this view. 3. Make the trigger notify the application program when it is trigged. Would this be possible? (I was planning to use Qt for application programming and database access.) Thanks, Erik ---(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] Database with "override" tables
Hello, I am in a situation where I have various tables (including data such as a product list) that are read-only to me. I wish to provide the functionality of changing this table: Removing items, modifying items, creating new ones. My original idea is to use a second table that is formatted similarly to the first, read-only table. However, I can't just duplicate all the data and work in a separate table, as the original table may be modified by an outside source at any time. Generally, I prefer to use the updated data from the read-only table, unless there has been reason to modify it in the past -- in which case the update may be safely ignored, and I would continue to use the second table. The most effective set up I have come up with thus far is as follows: CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT); CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id INT, desc TEXT); An entry in override_table with an initial_id matching an initial_id in initial_table would take precedence over the entry in initial_table. A fairly simple SELECT statement can return the data I require: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; So far so good, but then when I need to reference data in those two tables from somewhere else, I need to reference both initial_id and override_id on the result set from that table. I also end up using COALESCE an uncomfortable amount (though, if this is required I will do so). I would prefer to treat the initial + override tables as a single SELECT set, but cannot come up with a way to do this cleanly, especially with regards to the IDs. I am willing to abandon this format of "overriding" the initial table if it is way off-course, provided that the initial data be considered read-only and update-able in the absence of "override" data. All suggestions are greatly appreciated! Thanks in advance, Mike. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database query: Notification about change?
On Mon, Dec 05, 2005 at 19:22:22 +0100, Erik Sigra <[EMAIL PROTECTED]> wrote: > Hi, > I plan to develop an application that is somewhat like a spreadsheet > with cells containing formulas. When a cell value is changed, things > must be updated. But the formulas can contain database queries, which > means that the cell has to be notified when the database changes in such > a way that the result of the query changes. How is this done? I would > really like to avoid recalculating the whole thing for each change to > the database. > > I looked in my database book and read about materialized views and > triggers. It seems like I should do the following: > 1. Make a materialized view from the query. > 2. Add a trigger for changes to this view. > 3. Make the trigger notify the application program when it is trigged. > > Would this be possible? (I was planning to use Qt for application > programming and database access.) Postgres provides the NOTIFY command (http://developer.postgresql.org/docs/postgres/sql-notify.html) and you could use that in appropiate triggers to let your application know that it needs to refresh that values in at least some cells. You shouldn't need to use materialized views though. Just put the triggers on the base tables. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Database with "override" tables
I think I have a similar situation involving the naming of assets, where the usual asset description is used, but users can enter a description in a separate table which 'overrides' the original name with a name that is more familiar to the individual. IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two select statements, like this: select from foo1 union select from foo2 where ; Hope this helps. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Michael Burke Sent: Monday, December 05, 2005 2:07 PM To: PGSQL-SQL Subject: [SQL] Database with "override" tables Hello, I am in a situation where I have various tables (including data such as a product list) that are read-only to me. I wish to provide the functionality of changing this table: Removing items, modifying items, creating new ones. My original idea is to use a second table that is formatted similarly to the first, read-only table. However, I can't just duplicate all the data and work in a separate table, as the original table may be modified by an outside source at any time. Generally, I prefer to use the updated data from the read-only table, unless there has been reason to modify it in the past -- in which case the update may be safely ignored, and I would continue to use the second table. The most effective set up I have come up with thus far is as follows: CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT); CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id INT, desc TEXT); An entry in override_table with an initial_id matching an initial_id in initial_table would take precedence over the entry in initial_table. A fairly simple SELECT statement can return the data I require: SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN override_table ot ON it.initial_id = ot.initial_id; So far so good, but then when I need to reference data in those two tables from somewhere else, I need to reference both initial_id and override_id on the result set from that table. I also end up using COALESCE an uncomfortable amount (though, if this is required I will do so). I would prefer to treat the initial + override tables as a single SELECT set, but cannot come up with a way to do this cleanly, especially with regards to the IDs. I am willing to abandon this format of "overriding" the initial table if it is way off-course, provided that the initial data be considered read-only and update-able in the absence of "override" data. All suggestions are greatly appreciated! Thanks in advance, Mike. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] update question
I need a throw away value for an insert statement... example...update table set value = 1, value = 2, throw_away_value -- so i don't break the query where id = 1; Thanks Yahoo! Personals Let fate take it's course directly to your email. See who's waiting for you Yahoo! Personals
Re: [SQL] update question
On 12/5/05, Matthew Peter <[EMAIL PROTECTED]> wrote: > I need a throw away value for an insert statement... example... > > update table > set value = 1, value = 2, throw_away_value -- so i don't break the query > where id = 1; > > Thanks > What do you mean by "throw away value"? are you trying to update and if the row doesn't exist then insert? if that is you can do a function in plpgsql and use exceptions for that... there is an example in the manuals -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] update question
it's in a loop so there's an extra comma at the end so i was thinking i could put in a throw away value to keep the update from breaking if there's an additional commaJaime Casanova <[EMAIL PROTECTED]> wrote: On 12/5/05, Matthew Peter wrote:> I need a throw away value for an insert statement... example...>> update table> set value = 1, value = 2, throw_away_value -- so i don't break the query> where id = 1;>> Thanks>What do you mean by "throw away value"?are you trying to update and if the row doesn't exist then insert? ifthat is you can do a function in plpgsql and use exceptions forthat... there is an example in the manuals--regards,Jaime Casanova(DBA: DataBase Aniquilator ;)---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org Yahoo! Personals Single? There's someone we'd like you to meet. Lots of someones, actually. Yahoo! Personals
Re: [SQL] update question
Like WHERE 1 = 1, but in UPDATE table SET value = 1, 1 = 1; Yahoo! Personals Single? There's someone we'd like you to meet. Lots of someones, actually. Try Yahoo! Personals
Re: [SQL] update question
On 12/5/05, Matthew Peter <[EMAIL PROTECTED]> wrote: > it's in a loop so there's an extra comma at the end so i was thinking i > could put in a throw away value to keep the update from breaking if there's > an additional comma > > Jaime Casanova <[EMAIL PROTECTED]> wrote: > On 12/5/05, Matthew Peter wrote: > > I need a throw away value for an insert statement... example... > > > > update table > > set value = 1, value = 2, throw_away_value -- so i don't break the query > > where id = 1; > > > > Thanks > > > i guess you are constructing the query in a string and EXECUTEd it... if that is the case review your logic... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Database query: Notification about change?
Bruno Wolff III wrote: On Mon, Dec 05, 2005 at 19:22:22 +0100, Erik Sigra <[EMAIL PROTECTED]> wrote: Hi, I plan to develop an application that is somewhat like a spreadsheet with cells containing formulas. When a cell value is changed, things must be updated. But the formulas can contain database queries, which means that the cell has to be notified when the database changes in such a way that the result of the query changes. How is this done? I would really like to avoid recalculating the whole thing for each change to the database. I looked in my database book and read about materialized views and triggers. It seems like I should do the following: 1. Make a materialized view from the query. 2. Add a trigger for changes to this view. 3. Make the trigger notify the application program when it is trigged. Would this be possible? (I was planning to use Qt for application programming and database access.) Postgres provides the NOTIFY command (http://developer.postgresql.org/docs/postgres/sql-notify.html) and you could use that in appropiate triggers to let your application know that it needs to refresh that values in at least some cells. You shouldn't need to use materialized views though. Just put the triggers on the base tables. Thanks for the hint! I thought I need to put the triggers on materialized views. If I would have to put them on the base tables, the application program would have to understand the query to figure out which tables it uses. I really do not want to make a parser for SQL. What if the user of the spreadsheet-like application enters a formula containing a query like this: select distinct t1.person_id--, t1.sport_id, t2.sport_id from (select distinct sport_id, person_id from (-- hitta sporten för varje lopp_match select * from (select lopp_match_id, sport_id from ensamlopp_match natural join ensamgrentävling natural join grentävling natural join gren) as t union (select lopp_match_id, sport_id from laglopp_match natural join laggrentävling natural join grentävling natural join gren)) as t natural join resursbokning natural join personresurs natural join resurstyp where resurstyp_namn = 'domare') as t1, (select distinct sport_id, person_id from (-- hitta sporten för varje lopp_match select * from (select lopp_match_id, sport_id from ensamlopp_match natural join ensamgrentävling natural join grentävling natural join gren) as t union (select lopp_match_id, sport_id from laglopp_match natural join laggrentävling natural join grentävling natural join gren)) as t natural join resursbokning natural join personresurs natural join resurstyp where resurstyp_namn = 'domare') as t2 where t1.sport_id != t2.sport_id and t1.person_id = t2.person_id; ? (Example query taken from a university course project.) I do not want the application program to do anything more complex with the query than adding "create view as" in front of it and then add some trigger or whatever. And I think it will be much more efficient to watch a materialized view than to rerun the query whenever one of the base tables change. That is what materialized view optimization is about, right? A typical query might look like this: select sum(price * fraction_deductible) from year_expenditures; Thanks. I hope it is possible to understand what I wrote eventhough I do not know that much about practical database programming. Erik ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database query: Notification about change?
On Tue, Dec 06, 2005 at 02:00:18 +0100, Erik Sigra <[EMAIL PROTECTED]> wrote: > Thanks for the hint! I thought I need to put the triggers on > materialized views. If I would have to put them on the base tables, the > application program would have to understand the query to figure out > which tables it uses. I really do not want to make a parser for SQL. > What if the user of the spreadsheet-like application enters a formula > containing a query like this: I would say, that this is a really odd thing to do. If you are going to allow any old user written query, you are going to find it hard to update the cells at the time the data changes. It might be better to have the user hit a refresh button or to have timed refreshes. > And I think it will be much more efficient to watch a materialized view > than to rerun the query whenever one of the base tables change. That is > what materialized view optimization is about, right? A typical query Its pretty much the same thing. The materialized view is going to be maintained by triggers. Postgres doesn't have a built in way to automate this, so the application would have to parse the queries and create the trigger functions. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] update question
Matthew Peter <[EMAIL PROTECTED]> writes: > it's in a loop so there's an extra comma at the end so i was thinking i > could put in a throw away value to keep the update from breaking if there's > an additional comma The best thing is to fix your loop logic. Usually it's not hard to emit a comma only if one is needed. If you're intent on using a broken loop, you could do "foo = foo" where foo is any table column you didn't yet assign to. Consider though the corner case where you've already assigned all the columns. Best bet is to fix your loop... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
