From: Ange Michel POZZO <poange@technologist.com>
To: pgsql-sql@postgresql.org
To: pgsql-general@postgresql.org
To: pgsql-bugs@postgresql.org
Subject: problem with view and case - please help
Date: Fri, 21 Jul 2000 10:50:54 +0200
Message-ID: <39780EEE.BBCB6406@technologist.com> (raw)
I repost my message because it seems that my previous post don't go on
i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2]
rpm version of Linux Mandrake 7.02
i try this query :
[ange@ange ange]$ psql zonecommerce -h 192.0.1.84 -u -f toto.sql
Username: postgres
Password:
DROP VIEW liste_browser ;
ERROR: Rule or view 'liste_browser' not found <-------- this ok
because view does not exist at this time
CREATE VIEW browser
AS
SELECT
agent_i,
CASE
WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0'
WHEN agent_i LIKE '%MSIE 3.0;%' THEN 'Internet Explorer 3.0'
WHEN agent_i LIKE '%MSIE 3.0a;%' THEN 'Internet Explorer 3.0a'
WHEN agent_i LIKE '%MSIE 3.0B;%' THEN 'Internet Explorer 3.0B'
WHEN agent_i LIKE '%MSIE 3.01;%' THEN 'Internet Explorer 3.01'
WHEN agent_i LIKE '%MSIE 3.02;%' THEN 'Internet Explorer 3.02'
WHEN agent_i LIKE '%MSIE 4.0b1;%' THEN 'Internet Explorer 4.0 beta 1'
WHEN agent_i LIKE '%MSIE 4.0;%' THEN 'Internet Explorer 4.0'
WHEN agent_i LIKE '%MSIE 4.01;%' THEN 'Internet Explorer 4.01'
WHEN agent_i LIKE '%MSIE 4.5;%' THEN 'Internet Explorer 4.5'
WHEN agent_i LIKE '%MSIE 5.0b1;%' THEN 'Internet Explorer 5.0 beta 1'
WHEN agent_i LIKE '%MSIE 5.0b2;%' THEN 'Internet Explorer 5.0 beta 2'
WHEN agent_i LIKE '%MSIE 5.0;%' THEN 'Internet Explorer 5.0'
WHEN agent_i LIKE '%MSIE 5.01;%' THEN 'Internet Explorer 5.01'
WHEN agent_i LIKE '%MSIE 5.5b1;%' THEN 'Internet Explorer 5.5 beta 1'
WHEN agent_i LIKE '%MSIE 5.5;%' THEN 'Internet Explorer 5.5'
WHEN agent_i = 'Mozilla' THEN 'Netscape version inconnue'
WHEN agent_i LIKE 'Mozilla (X11; I; Linux 2.0.32 i586)%' AND agent_i NOT
LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape Linux
version non connue'
WHEN agent_i LIKE 'Mozilla/2.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.0'
WHEN agent_i LIKE 'Mozilla/2.02 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.02'
WHEN agent_i LIKE 'Mozilla/2.02E %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.02E'
WHEN agent_i LIKE 'Mozilla/3.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 3.0'
WHEN agent_i LIKE 'Mozilla/3.01 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape 3.01'
WHEN agent_i LIKE 'Mozilla/3.02 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/3.03 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.03 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.04 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.05 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.06 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.07 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.08 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.5 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.51 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.6 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.61 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.7 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.71 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.72 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/4.73 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND
agent_i NOT LIKE '%compatible%' THEN 'Netscape'
ELSE agent_i END AS navigateur, count (agent_i)
as total from access group by agent_i;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
[ange@ange ange]$
what is the problem with my query ?
is it a bug ?
another problem is that after backend crash, there is a file called
browser in this database directory :
[root@ange zonecommerce]# ls -l
total 131384
-rw------- 1 postgres postgres 4 Apr 6 13:12 PG_VERSION
-rw------- 1 postgres postgres 40960 Jul 20 19:30 abonnes
-rw------- 1 postgres postgres 8192 Jul 20 19:30
abonnes_id_abonne_seq
-rw------- 1 postgres postgres 40960 Jul 20 19:30
abonnes_mail_abonne_pk
-rw------- 1 postgres postgres 16384 Jul 20 19:30 abonnes_pk
-rw------- 1 postgres postgres 78299136 Jul 21 09:47 access
-rw------- 1 postgres postgres 98304 Jul 17 15:50 access2
-rw------- 1 postgres postgres 21954560 Jul 21 09:30
access3_filename_pkey
-rw------- 1 postgres postgres 15671296 Jul 21 09:30
access3_first_pkey
-rw------- 1 postgres postgres 24576 Jul 21 08:20 appartient
-rw------- 1 postgres postgres 49152 Jul 21 08:20 appartient_pk
-rw------- 1 postgres postgres 892928 Jul 21 09:47 assoc_37_fk
-rw------- 1 postgres postgres 393216 Jul 21 09:46 bannieres
-rw------- 1 postgres postgres 8192 Jul 17 11:48
bannieres_id_banniere_seq
-rw------- 1 postgres postgres 368640 Jul 21 09:46 bannieres_pk
-rw------- 1 postgres postgres 2424832 Jul 21 09:47 boutique
-rw------- 1 postgres postgres 8192 Jul 20 19:30
boutique_id_boutique_seq
-rw------- 1 postgres postgres 876544 Jul 21 09:47 boutique_pk
-rw------- 1 postgres postgres 0 Jul 21 09:53 browser
<------------here
:
and i must delete the file to create a table or a view with this name
because a DROP on this view don't work and i can't create the view
any help is welcome
thanks
Ange
--
******************************************************************************
POZZO Ange Michel
mail : ange@alpinfo.fr
Administrateur - Développeur
ALPINFO
617 Rue Denis Papin
73290 La Motte Servolex
Savoie - France
tel : 04 79 26 06 28
fax : 04 79 25 68 36
Zonecommerce, l'annuaire français du commerce électronique
- Plusieurs centaines de magasin référencé pour tous vos achats
sur internet, tous avec paiement sécurisé en ligne
- Des promotions proposées par les boutiques
- Vente au enchères, forum de discussion
- Des actualités, sports et loisirs, cinéma, horoscope ...
- Les iddées d'olivia, le site du mois, l'interview
- Des liens, des conseils ...
http://www.zonecommerce.com/
******************************************************************************
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: poange@technologist.com, pgsql-general@postgresql.org, pgsql-bugs@postgresql.org
Subject: Re: problem with view and case - please help
In-Reply-To: <39780EEE.BBCB6406@technologist.com>
References: <39780EEE.BBCB6406@technologist.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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