Thread: virtual table
Hi I have another virtual problem, currently without any examples ;-) Let's say we have some pl/pgsql function which puts result into table1. This flat table must be normalized and put into table2. Sometimes 1 row from table1 = 1 row from table2, but sometimes 1 row from table1= 3 rows from table2. Data from table1 are transferrend into table2 using triggers. I found, I don't to have any data in table1. The question is: Is it possible to create virtual table in Postgresql? Virtual - means it won't contain any columns nor data, but trigger doing all the job. Regards, Tomasz Myrta
On Sunday 22 Jun 2003 10:23 pm, Tomasz Myrta wrote: > Hi > I have another virtual problem, currently without any examples ;-) > > Let's say we have some pl/pgsql function which puts result into table1. > This flat table must be normalized and put into table2. Sometimes 1 row > from table1 = 1 row from table2, but sometimes 1 row from table1= 3 rows > from table2. Data from table1 are transferrend into table2 using triggers. > I found, I don't to have any data in table1. > The question is: Is it possible to create virtual table in Postgresql? > Virtual - means it won't contain any columns nor data, but trigger doing > all the job. Look into views - you'll need to provide triggers to handle the update/inserts. -- Richard Huxton
Dnia 2003-06-23 10:29, Użytkownik Richard Huxton napisał: > Look into views - you'll need to provide triggers to handle the > update/inserts. I think view won't change too much - there is not too much difference for this case between creating view and empty table with trigger returning null. I was thinking about something more comfortably - table without pre-declared columns so I don't have to change table definition when pl/pgsql function changes. Can anyone tell me how triggers work? Do they need to look into table column definition to retrieve data? Regards, Tomasz Myrta
On Monday 23 Jun 2003 9:39 am, Tomasz Myrta wrote: > Dnia 2003-06-23 10:29, Użytkownik Richard Huxton napisał: > > Look into views - you'll need to provide triggers to handle the > > update/inserts. > > I think view won't change too much - there is not too much difference for > this case between creating view and empty table with trigger returning > null. I was thinking about something more comfortably - table without > pre-declared columns so I don't have to change table definition when > pl/pgsql function changes. > > Can anyone tell me how triggers work? Do they need to look into table > column definition to retrieve data? Hmm - I think your problem is going to come before that. Any time you do an INSERT, PostgreSQL is going to need to know the types of all the columns involved. For this sort of thing, I try to keep all the related bits (initial function, views, triggers) in the same text-file to encourage me to remember to update them all together. It sounds like this table1 is only being used during transfer. Is there any reason why you aren't just inserting the required rows into table2. -- Richard Huxton
Dnia 2003-06-23 10:54, Użytkownik Richard Huxton napisał: > Hmm - I think your problem is going to come before that. Any time you do an > INSERT, PostgreSQL is going to need to know the types of all the columns > involved. > > For this sort of thing, I try to keep all the related bits (initial function, > views, triggers) in the same text-file to encourage me to remember to update > them all together. I also do this - it isn't really difficult with well written scripts. I was just wondering if I don't waste my time tracking all the changes. > > It sounds like this table1 is only being used during transfer. Is there any > reason why you aren't just inserting the required rows into table2. My queries are ordinary transportation problems with 0 up to 2 changes. I found it's better (for performance reason) to find all possible relations as single rows and split them at the end into simple connections. Maybe I should dig into this problem again and rewrite my queries... Regards, Tomasz Myrta
On Monday 23 Jun 2003 10:09 am, Tomasz Myrta wrote: > Dnia 2003-06-23 10:54, Użytkownik Richard Huxton napisał: > > Hmm - I think your problem is going to come before that. Any time you do > > an INSERT, PostgreSQL is going to need to know the types of all the > > columns involved. > > > > For this sort of thing, I try to keep all the related bits (initial > > function, views, triggers) in the same text-file to encourage me to > > remember to update them all together. > > I also do this - it isn't really difficult with well written scripts. I was > just wondering if I don't waste my time tracking all the changes. I know what you mean - except for simple cases I've never managed to automate type-checking my code without running it through PG. > > It sounds like this table1 is only being used during transfer. Is there > > any reason why you aren't just inserting the required rows into table2. > > My queries are ordinary transportation problems with 0 up to 2 changes. I > found it's better (for performance reason) to find all possible relations > as single rows and split them at the end into simple connections. Maybe I > should dig into this problem again and rewrite my queries... Not saying you've got the wrong approach. Might be worth a few minutes attention though -- Richard Huxton