pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedvirtual table
6+ messages / 2 participants
[nested] [flat]
* virtual table
@ 2003-06-22 21:23 Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:29 ` Re: virtual table Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2003-06-22 21:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: virtual table
2003-06-22 21:23 virtual table Tomasz Myrta <jasiek@klaster.net>
@ 2003-06-23 08:29 ` Richard Huxton <dev@archonet.com>
2003-06-23 08:39 ` Re: virtual table Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2003-06-23 08:29 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: virtual table
2003-06-22 21:23 virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:29 ` Re: virtual table Richard Huxton <dev@archonet.com>
@ 2003-06-23 08:39 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:54 ` Re: virtual table Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2003-06-23 08:39 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: virtual table
2003-06-22 21:23 virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:29 ` Re: virtual table Richard Huxton <dev@archonet.com>
2003-06-23 08:39 ` Re: virtual table Tomasz Myrta <jasiek@klaster.net>
@ 2003-06-23 08:54 ` Richard Huxton <dev@archonet.com>
2003-06-23 09:09 ` Re: virtual table Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2003-06-23 08:54 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: virtual table
2003-06-22 21:23 virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:29 ` Re: virtual table Richard Huxton <dev@archonet.com>
2003-06-23 08:39 ` Re: virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:54 ` Re: virtual table Richard Huxton <dev@archonet.com>
@ 2003-06-23 09:09 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-23 11:01 ` Re: virtual table Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2003-06-23 09:09 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: virtual table
2003-06-22 21:23 virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:29 ` Re: virtual table Richard Huxton <dev@archonet.com>
2003-06-23 08:39 ` Re: virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:54 ` Re: virtual table Richard Huxton <dev@archonet.com>
2003-06-23 09:09 ` Re: virtual table Tomasz Myrta <jasiek@klaster.net>
@ 2003-06-23 11:01 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2003-06-23 11:01 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-06-23 11:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-22 21:23 virtual table Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:29 ` Richard Huxton <dev@archonet.com>
2003-06-23 08:39 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-23 08:54 ` Richard Huxton <dev@archonet.com>
2003-06-23 09:09 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-23 11:01 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox