Re: [SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)

[Christoph Haller]

> My problem is that I need to fill in the gaps (the available rain data
in the
> corresponding stations), and this would be a very good output for me.
> I've prepared an UPDATE but it doesn't work. Perhaps someone could
tell me
> where is the error (I've not a very good knowledge of Postgresql). The
UPDATE
> is based on the results of the query:
>
>


> UPDATE  series_lluvia SET st7237=rain FROM
>
> /* here begins the SELECT to obtain the series for one rain gauge
station;
> and it works right
>  from here to the next comment */
> SELECT cod_station, year, month, day, rain FROM (
>
> SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as
rain
> FROM pluviometria WHERE ten=1
> UNION ALL
> ...
> SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11
as rain
> FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL
> ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238

>
> /* the SELECT has finished here */
> WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month
AND
> series_lluvia.day=temp.day;
> ---
>
> Please can you tell me if the syntax of this UPDATE is correct? (Can I
use
> the results of a SELECT to UPDATE a table?)
>
Javier,
I've seen something similar on the list in January this year.
Maybe it works. Try

UPDATE  series_lluvia SET st7237=(
 SELECT rain FROM (

 SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as
rain
 FROM pluviometria WHERE ten=1
 UNION ALL
 ...
 SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as
rain
 FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL
 ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7238
 )
 WHERE series_lluvia.year=temp.year AND series_lluvia.month=temp.month
AND
 series_lluvia.day=temp.day;

Regards, Christoph




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] join and dynamic view

[Gary Stainburn]

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 (
rsidint4 not null references staff(sid),
rdidcharacter 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 


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Difference between DB2 7.0 & latest version of PostgresSQL?

[Tarun Galarani]

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


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] join and dynamic view

[Christoph Haller]

>
> 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


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] join and dynamic view

[Gary Stainburn]

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 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Difference between DB2 7.0 & latest version of PostgresSQL?

[Tarun Galarani]

Hello

Thanks Hans-Jürgen for quick reply. Would you tell me how can I import a
database from DB2 to Postgres.

Regards,
Tarun Galrani



- Original Message -
From: "Hans-Jürgen Schönig" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, December 17, 2002 5:18 PM
Subject: Re: Difference between DB2 7.0 & latest version of PostgresSQL?


Tarun Galarani wrote:

>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
>
>
>
>

Hello ...

We have done a lot of migration work so far. Switching from DB2 to
PostgreSQL ist not that kind of a problem. PostgreSQL is very close to
the ANSI standard.

What you really need is expert knowledge about PostgreSQL
(administration, security, tuning, and so forth).

Some features and functions of DB2 can easily be modelled in PostgreSQL.
If there is something we can do for you, feel free to contact us.

Best regards,

Hans-Jürgen Schönig

--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at , cluster.postgresql.at
, www.cybertec.at
, kernel.cybertec.at 





---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] join and dynamic view

[Christoph Haller]

> 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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] join and dynamic view

[Gary Stainburn]

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_paramTEXT;
 ranks_record   RECORD;
 create_viewTEXT;
 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 FUNC

Re: [SQL] join and dynamic view

[Tomasz Myrta]

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



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] join and dynamic view

[Christoph Haller]

>
> 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_paramTEXT;
 ranks_record   RECORD;
 create_viewTEXT;
 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

Re: [SQL] join and dynamic view

[Tomasz Myrta]

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


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] join and dynamic view

[Christoph Haller]

>
> 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



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] join and dynamic view

[Gary Stainburn]

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_paramTEXT;
>  ranks_record   RECORD;
>  create_viewTEXT;
>  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 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] join and dynamic view

[Tomasz Myrta]

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



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Upgrade question - was Re: [SQL] join and dynamic view

[Gary Stainburn]

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 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: Upgrade question - was Re: [SQL] join and dynamic view

[Christoph Haller]

>
> 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 dump
before any kind of upgrade.
Regards, Christoph



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Upgrade question - was Re: [SQL] join and dynamic view

[Andreas Joseph Krogh]

-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.
> >
> > Will I need to dump/restore the database for this upgrade?

As allways when version-upgrading(and not patch-level), you need to 
dump/restore as the binary on-disk format changes.

- -- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
The difference between insanity and genius is measured by success
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9/0GGUopImDh2gfQRAgJJAJ46UWmX4OBWogSHQOGOsO5w1+ZEDQCfcTFy
XpXZyDZfRZAAHs1i7lixKQQ=
=yI3q
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] handling error in a function

[Peter Gabriel]

Hi all!

i made desperate efforts with handling errors in a function.

I am using functions for encapsulating a few sql-statements. Please have a
look at this:

CREATE FUNCTION sp_fdelce(int4) RETURNS int4 AS '
DECLARE
 id ALIAS FOR $1;
BEGIN
   DELETE FROM f_ces WHERE fce_id = id;
   
   -- "virtual code follows"
   IF ERROR
RETURN 0;
   ELSE
  RETURN 1;
END;
'
LANGUAGE 'plpgsql';

Not difficult. I know. But fce_id is used as a foreign key by other tables.
When executing this and violating that constraint (i mustn't delete that
row), the function aborts with "unknown error" and i have no way to return 0 or
something like that. 

I am programming with PHP and PEAR, each time, the result set is an object
of type error, the script jumps to an error page, to calm the angry customers.


Especially in this case I don't want to jump to the error page, i want to
tell the user with a normal Messageline: Sorry, you mustn't delete that
element.

I can't handle this error? Is that right? I really have no way to catch that
foreign key violence? 

(In MS-SQL there is @@ERROR to indicate a database error and i can handle
it, in Oracle nearly the same ...)

Please help :-)

Regards,
peter


-- 
+++ GMX - Mail, Messaging & more  http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] pl/pgsql question

[Tim Perdue]

I have created a function in pl/pgsql to modify a row before it gets put 
into the database, but it seems my modification is being ignored, and 
the unmodified row is being inserted.

I have confirmed with this RAISE EXCEPTION that my "NEW" row is modified 
properly, however it is not being stored in the db.

NEW.start_date := NEW.start_date+delta;
--  RAISE EXCEPTION ''new start date: % '',NEW.start_date;
NEW.end_date := NEW.end_date+delta;

It's probably something very obvious, but I'm mystified.

Tim


--
--  Function to enforce dependencies in the table structure
--
CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
DECLARE
dependent RECORD;
delta INTEGER;
BEGIN
--
--  First make sure we start on or after end_date of tasks
--  that we depend on
--
FOR dependent IN SELECT * FROM project_depend_vw
WHERE 
project_task_id=NEW.project_task_id LOOP
--
--  See if the task we are dependent on
--  ends after we are supposed to start
--
IF dependent.end_date > NEW.start_date THEN
delta := dependent.end_date-NEW.start_date;
--  RAISE EXCEPTION ''delta: % '',delta;
NEW.start_date := NEW.start_date+delta;
--  RAISE EXCEPTION ''new start date: % 
'',NEW.start_date;
NEW.end_date := NEW.end_date+delta;
END IF;

END LOOP;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task
FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [SQL] pl/pgsql question

[Tom Lane]

Tim Perdue <[EMAIL PROTECTED]> writes:
> I have created a function in pl/pgsql to modify a row before it gets put 
   ^^^
> into the database, but it seems my modification is being ignored, and 
> the unmodified row is being inserted.

> CREATE TRIGGER projtask_insert_depend_trig AFTER INSERT ON project_task
 
>  FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();

I believe I see your problem ...

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] pl/pgsql question

[Ludwig Lim]

--- Tim Perdue <[EMAIL PROTECTED]> wrote:
> I have created a function in pl/pgsql to modify a
> row before it gets put 
> into the database, but it seems my modification is
> being ignored, and 
> the unmodified row is being inserted.
> 
> I have confirmed with this RAISE EXCEPTION that my
> "NEW" row is modified 
> properly, however it is not being stored in the db.
> 
> NEW.start_date := NEW.start_date+delta;
> --  RAISE EXCEPTION ''new start date: %
> '',NEW.start_date;
> NEW.end_date := NEW.end_date+delta;
> 
> It's probably something very obvious, but I'm
> mystified.
> 
> CREATE TRIGGER projtask_insert_depend_trig AFTER
> INSERT ON project_task
>  FOR EACH ROW EXECUTE PROCEDURE
> projtask_insert_depend();
> 
> 

  Try changing the "AFTER" to "BEFORE"

CREATE TRIGGER projtask_insert_depend_trig BEFORE...

Changes made to the "NEW" will not be reflect in the
AFTER trigger since, the row is already inserted.

__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Postgres V/S Oracle

[Prashanth - Kamath]

Hi,
I am working in the database area for the first time.
My work is related to Postgres. Now comparatively i am familiar 
with the Postgres.
I am looking for any document on differences between postgres and 
oracle w.r.t SQL syntax and built-in functions.
Any pointers in this direction will be of great help.

Regards

Prashanth Kamath





---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Postgres V/S Oracle

[Josh Berkus]

Prashanth,

> I am working in the database area for the first time.
> My work is related to Postgres. Now comparatively i am familiar with
> the Postgres.
> I am looking for any document on differences between postgres and
> oracle w.r.t SQL syntax and built-in functions.
> Any pointers in this direction will be of great help.

1) http://techdocs.postgresql.org
2) O'Reilly's "SQL In a Nutshell"  (over a year old, but better than
nothing)

-Josh

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Dynamic sql program using libpq

[Prashanth - Kamath]

Hi,
I am new to the embeeded sql programming. I am able to find couple 
of example for dynamic sql programming through ecpg. But i want to 
do dynamic sql programming through libpq.
If anyone has program doing the dynamic sql programmming using the 
libpq libraries please mail to me.Even pointers on the net are 
welcome.

Regards
Prashanth Kamath


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html