Re: join and dynamic view - Mailing list pgsql-sql

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