pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin and dynamic view
11+ messages / 3 participants
[nested] [flat]
* join and dynamic view
@ 2002-12-17 10:58 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Gary Stainburn @ 2002-12-17 10:58 UTC (permalink / raw)
To: pgsql-sql
Hi folks
is it possible to make a dynamically declare a view based on a table?
I have 3 tables
create table depts (
did character unique not null, -- key
dsdesc character (3), -- short desc
ddesc character varying(40) -- long desc
);
create table staff (
sid int4 not null unique, -- key
sname character varying(40), -- name
);
create table ranks (
rsid int4 not null references staff(sid),
rdid character not null references depts(did),
rrank int4 not null,
primary key (rsid, rdid)
);
copy "depts" from stdin;
O OPS Operations
M MPD Motive Power Dept
\.
copy "staff" from stdin;
1 Rod
2 Jayne
3 Freddie
\.
copy "ranks" from stdin;
1 M 3
2 M 2
2 O 5
3 O 3
\.
Is it possible to now define a view such that it returns:
select * from myview;
sid | Name | OPS | MPD
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 2 | 5
3 | Freddie | 3 |
and if I add another row to depts, that the new row would be included?
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 12:06 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 11+ messages in thread
From: Christoph Haller @ 2002-12-17 12:06 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; +Cc: pgsql-sql
>
> is it possible to make a dynamically declare a view based on a table?
>
Yes, by all means.
>
> Is it possible to now define a view such that it returns:
>
> select * from myview;
> sid | Name | OPS | MPD
> -----+---------+-----+-----
> 1 | Rod | | 3
> 2 | Jayne | 2 | 5
> 3 | Freddie | 3 |
>
> and if I add another row to depts, that the new row would be included?
>
^^^^^^^^^^^^^^^^ you mean column, don't you?
The closest query I can get so far is
SELECT staff.*,
CASE dsdesc WHEN 'OPS' THEN rrank ELSE NULL END AS "OPS",
CASE dsdesc WHEN 'MPD' THEN rrank ELSE NULL END AS "MPD"
FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ;
sid | sname | OPS | MPD
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | | 2
2 | Jayne | 5 |
3 | Freddie | 3 |
(4 rows)
but
sid | sname | OPS | MPD
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 5| 2
3 | Freddie | 3 |
(3 rows)
is what you want (I suppose Jayne's 2 in OPS and 5 in MPD is a mismatch
of yours).
As soon as you are somebody else can tell me how to merge Jayne's two
rows into one,
I'm sure I can write a plpgsql function to dynamically create the view
you're looking for.
Regards, Christoph
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 12:23 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 11+ messages in thread
From: Gary Stainburn @ 2002-12-17 12:23 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
Hi Christoph,
On Tuesday 17 Dec 2002 12:06 pm, Christoph Haller wrote:
> > is it possible to make a dynamically declare a view based on a table?
>
> Yes, by all means.
>
> > Is it possible to now define a view such that it returns:
> >
> > select * from myview;
> > sid | Name | OPS | MPD
> > -----+---------+-----+-----
> > 1 | Rod | | 3
> > 2 | Jayne | 2 | 5
> > 3 | Freddie | 3 |
> >
> > and if I add another row to depts, that the new row would be included?
>
> ^^^^^^^^^^^^^^^^ you mean column, don't you?
What I mean here was that if I add another row to the depts table, e.g.
A ADM Administrative
I would like the ADM column to automatically appear in the 'myview' view
without having to recreate the view - i.e. the rows in the 'depts' table
become columns in 'myview' view
> The closest query I can get so far is
> SELECT staff.*,
> CASE dsdesc WHEN 'OPS' THEN rrank ELSE NULL END AS "OPS",
> CASE dsdesc WHEN 'MPD' THEN rrank ELSE NULL END AS "MPD"
> FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ;
Surely the problem with this is that I'd have to drop/amend/create the view
every time I add a row to 'depts'. Couldn't I just do that using an outer
join instead of a case?
>
> sid | sname | OPS | MPD
> -----+---------+-----+-----
> 1 | Rod | | 3
> 2 | Jayne | | 2
> 2 | Jayne | 5 |
> 3 | Freddie | 3 |
> (4 rows)
>
> but
>
> sid | sname | OPS | MPD
> -----+---------+-----+-----
> 1 | Rod | | 3
> 2 | Jayne | 5| 2
> 3 | Freddie | 3 |
> (3 rows)
>
> is what you want (I suppose Jayne's 2 in OPS and 5 in MPD is a mismatch
> of yours).
Yes it was, sorry.
> As soon as you are somebody else can tell me how to merge Jayne's two
> rows into one,
> I'm sure I can write a plpgsql function to dynamically create the view
> you're looking for.
How could a plpgsql dynamically create the view?
How about a trigger from the on-update of the depts table to drop the view and
then create a new one. Could it not do the same thing using outer joins.
(I've done VERY little plpgsql and even less with triggers.
>
> Regards, Christoph
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 13:06 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 11+ messages in thread
From: Christoph Haller @ 2002-12-17 13:06 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; +Cc: pgsql-sql
> As soon as you or somebody else can tell me how to merge Jayne's two
> rows into one,
> I'm sure I can write a plpgsql function to dynamically create the view
> you're looking for.
Ok, got it:
SELECT sid, sname, SUM("OPS") AS "OPS", SUM("MPD") AS "MPD" FROM (
SELECT staff.*,
CASE dsdesc WHEN 'OPS' THEN rrank ELSE 0 END AS "OPS",
CASE dsdesc WHEN 'MPD' THEN rrank ELSE 0 END AS "MPD"
FROM staff,depts,ranks WHERE sid=rsid AND did=rdid ) as foo
GROUP BY sid, sname ;
sid | sname | OPS | MPD
-----+---------+-----+-----
1 | Rod | 0 | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 | 0
(3 rows)
Gary,
I'm going to write the plpgsql function to dynamically amend the view.
In the meantime you may think about creating a trigger which fires every
time a new department is entered and which calls the function then.
Regards, Christoph
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 13:09 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Gary Stainburn @ 2002-12-17 13:09 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
Thanks for that Christoph.
I've got the view I need :
create view users as
select s.*, o.rrank as ops, m.rrank as mpd from staff s
left outer join ranks o on o.rsid = s.sid and o.rdid = 'O'
left outer join ranks m on m.rsid = s.sid and m.rdid = 'M';
which provides:
garytest=# select * from users;
sid | sname | ops | mpd
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 |
(3 rows)
garytest=#
I've now started amending your plpgsql script to create this, but as you can
see I've cocked up somewhere. I wonder if you could have a peek at it for
me.
create_users_view() returns integer as '
DECLARE
pg_views_rtype pg_views%ROWTYPE;
vname_param TEXT;
ranks_record RECORD;
create_view TEXT;
join_text TEXT;
BEGIN
vname_param:=''users'';
SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
END IF;
create_view :=
''CREATE VIEW '' || quote_ident(vname_param) ||
'' AS SELECT s.* '';
join_text:='' from staff s '';
FOR ranks_record IN
EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;''
LOOP
create_view :=
create_view || '', '' || ranks_record.dsdesc ||
'' AS '' || ranks_record.did);
join_text :=
join_text || '' left outer join ranks '' || ranks_record.did ||
'' ON '' || ranks_record.did || ''.rsid = s.sid and '' ||
ranks_record.did || ''.rdid = '''' || ranks_record.did '''' ;
END LOOP;
create_view :=
create_view || join_text || '';'';
EXECUTE create_view ;
RETURN 0;
END;
' LANGUAGE 'plpgsql' ;
ERROR: parser: parse error at or near "or"
On Tuesday 17 Dec 2002 12:40 pm, Christoph Haller wrote:
> > What I mean here was that if I add another row to the depts table,
>
> e.g.
>
> > A ADM Administrative
> >
> > I would like the ADM column to automatically appear in the 'myview'
>
> view
>
> > without having to recreate the view - i.e. the rows in the 'depts'
>
> table
>
> > become columns in 'myview' view
>
> Yes, that's what I thought you intended.
>
> > Surely the problem with this is that I'd have to drop/amend/create
>
> the view
>
> > every time I add a row to 'depts'. Couldn't I just do that using an
>
> outer
>
> > join instead of a case?
>
> Possibly, but so far I've no idea how to achieve that.
>
> > How could a plpgsql dynamically create the view?
> > How about a trigger from the on-update of the depts table to drop the
>
> view and
>
> > then create a new one. Could it not do the same thing using outer
>
> joins.
>
> > (I've done VERY little plpgsql and even less with triggers.
>
> I've done VERY little with triggers, too.
> But, how to dynamically create a view, see for yourself:
>
>
> Based on the e-mails on "Generating a cross tab (pivot table)",
> I can give you a PLpgSQL procedure to automatically generate a
> cross tab from any relation now.
> It's my first steps in PLpgSQL. I am pretty sure this is not the
> best way to implement, but I wanted to get some experience, so I
> did it this way.
>
> For all, who missed it last week, again the objective:
> There is a relation "sales",
> holding the sales of different products of different vendors.
> The task is to generate a report which shows the sales
> of every vendor and every product.
>
> Consider the following table populated with some data:
> CREATE TABLE sales (
> product TEXT,
> vendor TEXT,
> sales INTEGER
> );
>
> INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ;
> INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ;
> INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ;
> INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ;
> INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ;
> INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ;
> INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ;
> The following query generates the report:
> CREATE VIEW sales_report AS
> SELECT product,
> SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr.
> pink ",
> SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr.
> brown",
> SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr.
> green",
> SUM(sales) AS "sum of sales"
> FROM sales GROUP BY product ;
> SELECT * FROM sales_report ;
>
> product | mr. pink | mr. brown | mr. green | sum of sales
> ---------+-----------+-----------+-----------+--------------
> butter | 17 | 2 | 0 | 19
> honey | 19 | 0 | 2 | 21
> milk | 12 | 8 | 34 | 54
> (3 rows)
> It's obvious this approach is most inflexible.
> As soon as there is a new vendor, one has to re-write the query and add
> SUM(CASE vendor WHEN 'mr. new' THEN ... ,
>
> So what we need is a tool to automatically adapt the view to new vendors
>
> resp. new products.
> Here it is (choosing good mnemonics is not my favourite discipline):
>
> CREATE OR REPLACE FUNCTION
> create_pivot_report(TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS INTEGER AS '
> DECLARE
> pg_views_rtype pg_views%ROWTYPE;
> vname_param ALIAS FOR $1;
> pivot_column ALIAS FOR $2;
> select_column ALIAS FOR $3;
> pivot_table ALIAS FOR $4;
> aggregate_func ALIAS FOR $5;
> aggr_column ALIAS FOR $6;
> pivot_record RECORD;
> create_view TEXT;
> BEGIN
>
> SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
>
> IF FOUND THEN
> EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
> END IF;
> create_view :=
> ''CREATE VIEW '' || quote_ident(vname_param) ||
> '' AS SELECT '' || quote_ident(select_column) ;
> FOR pivot_record IN
> EXECUTE ''SELECT DISTINCT CAST(''
>
> || quote_ident(pivot_column)
> || '' AS TEXT) AS col1 FROM ''
> || quote_ident(pivot_table)
> ||
> || '' ORDER BY '' || quote_ident(pivot_column)
>
> LOOP
> create_view :=
> create_view || '','' || aggregate_func ||
> ''(CASE '' || quote_ident(pivot_column) ||
> '' WHEN '' || quote_literal(pivot_record.col1) ||
> '' THEN '' || quote_ident(aggr_column) ||
> '' ELSE 0 END) AS "'' || pivot_record.col1 || ''"'' ;
> END LOOP;
> create_view :=
> create_view || '','' || aggregate_func ||
> ''('' || quote_ident(aggr_column) || '') AS "'' || aggregate_func ||
> '' of '' || aggr_column || ''" FROM '' || quote_ident(pivot_table) ||
> '' GROUP BY '' || quote_ident(select_column);
> EXECUTE create_view ;
>
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> -- where
> -- vname_param ALIAS FOR $1; -- the view's name to create
> -- pivot_column ALIAS FOR $2; -- the pivot column (entries to be
> CASEd)
> -- select_column ALIAS FOR $3; -- the select column (entries to be
> grouped)
> -- pivot_table ALIAS FOR $4; -- the name of the table to work on
> -- aggregate_func ALIAS FOR $5; -- the name of the aggregate function
> -- aggr_column ALIAS FOR $6; -- the aggregate column (entries to be
> aggregated)
>
> First try:
> SELECT create_pivot_report
> ('sales_report2','vendor','product','sales','sum','sales');
> SELECT * FROM sales_report2 ;
> gives you 'sales_report2' as a copy of 'sales_report'.
>
> Now add another data set:
> INSERT INTO sales VALUES ( 'butter', 'mr. blue' , 11 ) ;
> Re-write the view by:
> SELECT create_pivot_report
> ('sales_report2','vendor','product','sales','sum','sales');
> And here we go
> SELECT * FROM sales_report2 ;
> product | mr. blue | mr. brown | mr. green | mr. pink | sum of sales
> ---------+----------+-----------+-----------+----------+--------------
> butter | 11 | 2 | 0 | 17 | 30
> honey | 0 | 0 | 2 | 19 | 21
> milk | 0 | 8 | 34 | 12 | 54
> (3 rows)
>
> More examples:
> SELECT create_pivot_report
> ('sales_report3','vendor','product','sales','avg','sales');
> SELECT create_pivot_report
> ('sales_report4','vendor','product','sales','stddev','sales');
> SELECT create_pivot_report
> ('sales_report5','product','vendor','sales','sum','sales');
> SELECT create_pivot_report
> ('sales_report6','product','vendor','sales','max','sales');
> SELECT create_pivot_report
> ('sales_report7','vendor','product','sales','max','sales');
>
> As you can see even interchanging the pivot column and the select column
>
> works. Feel free to use the code.
>
> Regards, Christoph
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 13:36 Tomasz Myrta <jasiek@klaster.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Tomasz Myrta @ 2002-12-17 13:36 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
Gary Stainburn wrote:
> How could a plpgsql dynamically create the view?
> How about a trigger from the on-update of the depts table to drop the
> view and
> then create a new one. Could it not do the same thing using outer joins.
I don't think it's good idead to do this, but you can recreate views
inside trigger on insert/update into depts. It would look like this (it
has a lot of errors ;-) ):
We have to change this query into dynamical plpgsql:
select sid,sname
,ranks_ops.rrank as ops --!!! column names !!!
,ranks_mpd.rrank as mpd
...
from
staff s
left join ranks as ranks_ops --!!! joins !!!
on (s.sid=ranks_ops.sid and ranks_ops.rdid='O')
left join ranks as ranks_ops
on (s.sid=ranks_ops.sid and ranks_ops.rdid='M')
...
Here is the solution:
CREATE OR REPLACE FUNCTION after_depts_change() RETURNS opaque AS '
DECLARE
table_alias varchar;
column_names varchar;
joins varchar;
x RECORD;
BEGIN
column_names='';
joins='';
for x in select * from depts
loop
table_alias=''ranks_'' || x.dsdesc;
column_names=column_names || '','' ||
table_alias || ''.rrank as '' || x.dsdesc;
joins=joins || ''left join ranks as '' || table_alias ||
'' on (s.sid='' || table_alias || ''.sid and " || table_alias ||
''.rdid='''' || x.did || '''') '';
end loop;
execute ''drop view myview; create view myview as select sid,sname''
|| column_names || '' from staff s '' || joins;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
DROP TRIGGER depts_change on depts;
CREATE TRIGGER depts_change AFTER insert or update or delete on depts
for each row execute procedure after_depts_change();
Tomasz Myrta
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 13:45 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 2 replies; 11+ messages in thread
From: Christoph Haller @ 2002-12-17 13:45 UTC (permalink / raw)
To: gary.stainburn@ringways.co.uk; +Cc: pgsql-sql; jasiek@klaster.net
>
> I've now started amending your plpgsql script to create this, but as
you can
> see I've cocked up somewhere. I wonder if you could have a peek at it
for
> me.
>
Gary,
CREATE OR REPLACE FUNCTION
create_users_view() returns integer as '
DECLARE
pg_views_rtype pg_views%ROWTYPE;
vname_param TEXT;
ranks_record RECORD;
create_view TEXT;
join_text TEXT;
BEGIN
vname_param:=''users'';
SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
IF FOUND THEN
EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
END IF;
create_view :=
''CREATE VIEW '' || quote_ident(vname_param) ||
'' AS SELECT s.* '';
join_text:='' from staff s '';
FOR ranks_record IN
EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;''
LOOP
create_view :=
create_view || '', '' || ranks_record.did ||
''.rrank AS '' || ranks_record.dsdesc;
join_text :=
join_text || '' left outer join ranks '' || ranks_record.did ||
'' ON '' || ranks_record.did || ''.rsid = s.sid and '' ||
ranks_record.did || ''.rdid = '' || quote_literal(ranks_record.did)
;
END LOOP;
create_view :=
create_view || join_text || '';'';
EXECUTE create_view ;
RETURN 0;
END;
' LANGUAGE 'plpgsql' ;
should work.
> I don't think it's good idea to do this, but you can recreate views
> inside trigger on insert/update into depts.
Tomasz,
Could you please point out why this is not a good idea. Thanks.
Regards, Christoph
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 14:11 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 1 reply; 11+ messages in thread
From: Gary Stainburn @ 2002-12-17 14:11 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql; jasiek@klaster.net
Hi Christoph, Tomasz,
Thanks to you both, I now have:
garytest=# select * from users;
sid | sname | ops | mpd
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 5 | 2
3 | Freddie | 3 |
(3 rows)
garytest=# insert into depts values ('A', 'ADM', 'Administrative');
INSERT 237559 1
garytest=# select * from users;
sid | sname | adm | mpd | ops
-----+---------+-----+-----+-----
1 | Rod | | 3 |
2 | Jayne | | 2 | 5
3 | Freddie | | | 3
(3 rows)
garytest=#
I found that the compile error complaining about the 'OR' was on the
CREATE OR REPLACE FUNCTION
line. I removed the 'OR REPLACE' and everything worked fine.
Also I had to change the returns to 'opaque' and 'return 0' to 'return null'
Thanks again
Gary
On Tuesday 17 Dec 2002 1:45 pm, Christoph Haller wrote:
> > I've now started amending your plpgsql script to create this, but as
>
> you can
>
> > see I've cocked up somewhere. I wonder if you could have a peek at it
>
> for
>
> > me.
>
> Gary,
>
> CREATE OR REPLACE FUNCTION
> create_users_view() returns integer as '
> DECLARE
> pg_views_rtype pg_views%ROWTYPE;
> vname_param TEXT;
> ranks_record RECORD;
> create_view TEXT;
> join_text TEXT;
> BEGIN
>
> vname_param:=''users'';
>
> SELECT INTO pg_views_rtype * FROM pg_views WHERE viewname = vname_param;
>
> IF FOUND THEN
> EXECUTE ''DROP VIEW '' || quote_ident(vname_param) ;
> END IF;
> create_view :=
> ''CREATE VIEW '' || quote_ident(vname_param) ||
> '' AS SELECT s.* '';
> join_text:='' from staff s '';
> FOR ranks_record IN
> EXECUTE ''SELECT did, dsdesc from depts ORDER BY did;''
> LOOP
> create_view :=
> create_view || '', '' || ranks_record.did ||
> ''.rrank AS '' || ranks_record.dsdesc;
> join_text :=
> join_text || '' left outer join ranks '' || ranks_record.did ||
> '' ON '' || ranks_record.did || ''.rsid = s.sid and '' ||
> ranks_record.did || ''.rdid = '' || quote_literal(ranks_record.did)
> ;
> END LOOP;
> create_view :=
> create_view || join_text || '';'';
> EXECUTE create_view ;
>
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql' ;
>
> should work.
>
> > I don't think it's good idea to do this, but you can recreate views
> > inside trigger on insert/update into depts.
>
> Tomasz,
> Could you please point out why this is not a good idea. Thanks.
>
> Regards, Christoph
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 14:12 Tomasz Myrta <jasiek@klaster.net>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 0 replies; 11+ messages in thread
From: Tomasz Myrta @ 2002-12-17 14:12 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: gary.stainburn@ringways.co.uk; pgsql-sql
Christoph Haller wrote:
> Tomasz,
> Could you please point out why this is not a good idea. Thanks.
How often do you chage structure of this view? What happens when during
querying this view someone recreates it?
What happens to your reports? Do you have them already dynamic?
Usually I create A4-paper based reports, so it is difficult to fit them
if horizontal structure changes.
Maybe creating dynamic view it is not so bad idea. I think you should
watch them carefully so they don't surprise you.
Tomasz Myrta
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 14:15 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 11+ messages in thread
From: Christoph Haller @ 2002-12-17 14:15 UTC (permalink / raw)
To: pgsql-sql
>
> Christoph Haller wrote:
>
> > Tomasz,
> > Could you please point out why this is not a good idea. Thanks.
>
> How often do you change structure of this view? What happens when
during
> querying this view someone recreates it?
>
> What happens to your reports? Do you have them already dynamic?
> Usually I create A4-paper based reports, so it is difficult to fit
them
> if horizontal structure changes.
>
> Maybe creating dynamic view is not so bad idea. I think you should
> watch them carefully so they don't surprise you.
>
> Tomasz Myrta
>
Good Points. Thanks again.
Regards, Christoph
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: join and dynamic view
@ 2002-12-17 14:31 Tomasz Myrta <jasiek@klaster.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Tomasz Myrta @ 2002-12-17 14:31 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
Gary Stainburn wrote:
>
> I found that the compile error complaining about the 'OR' was on the
>
> CREATE OR REPLACE FUNCTION
>
> line. I removed the 'OR REPLACE' and everything worked fine.
OR REPLACE is since postgres 7.2
>
>
> Also I had to change the returns to 'opaque' and 'return 0' to 'return
> null'
In this case it's ok to "return null", but if you create "before"
trigger you shoud "return new", because "return null" forces postgres
not to insert any data.
Tomasz Myrta
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2002-12-17 14:31 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-17 10:58 join and dynamic view Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-12-17 12:06 Re: join and dynamic view Christoph Haller <ch@rodos.fzk.de>
2002-12-17 12:23 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-12-17 13:36 ` Tomasz Myrta <jasiek@klaster.net>
2002-12-17 13:06 Re: join and dynamic view Christoph Haller <ch@rodos.fzk.de>
2002-12-17 13:09 Re: join and dynamic view Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-12-17 13:45 Re: join and dynamic view Christoph Haller <ch@rodos.fzk.de>
2002-12-17 14:11 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-12-17 14:31 ` Tomasz Myrta <jasiek@klaster.net>
2002-12-17 14:12 ` Tomasz Myrta <jasiek@klaster.net>
2002-12-17 14:15 Re: join and dynamic view Christoph Haller <ch@rodos.fzk.de>
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