Thread: join and dynamic view
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 | | 32 | Jayne | 2 | 53 | 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
Hello We are planning to shift from DB2 to PostgresSQL. What type of problem we can face? Where can I find the difference between IBM DB2 7.0 & latest version of PostgresSQL? Regards Tarun Galrani (Sr. Software Engineer) Waterford India Institute B-5 Pasayadan Panch Pakhadi Thane(W) Ph: 022-25 34 02 75
> > 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 ELSENULL 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
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
> 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
0END 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
Thanks for that Christoph.
I've got the view I need :
create view users asselect s.*, o.rrank as ops, m.rrank as mpd from staff sleft outer join ranks o on o.rsid = s.sid
ando.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 '
DECLAREpg_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
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.sidand 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 ranksas '' || 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
> > 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 ' DECLAREpg_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
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
> > 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
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
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
On Tuesday 17 Dec 2002 2:31 pm, Tomasz Myrta wrote: > 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 That explains it - the server I'm developing on is quite old - I didn't realise how old. I'm about to do an upgrade from 7.1.3 to 7.2.1-5 over christmas in fact. Will I need to dump/restore the database for this upgrade? > > > 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 -- 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