-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 17 December 2002 16:09, Christoph Haller wrote:
> > 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.
> >
>
>
> 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?
>
I'm not sure. But I think it's never ever a bad idea to do a du
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 ex
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
nul
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');
I
>
> 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?
>
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-
>
> 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
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
in
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
> 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
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
> > -
>
> 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
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 (
si
14 matches
Mail list logo
