Skip site navigation (1) Skip section navigation (2)

problem with view and case - please help

From: Ange Michel POZZO <poange(at)technologist(dot)com>
To: pgsql-sql(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org
Subject: problem with view and case - please help
Date: 2000-07-21 08:50:54
Message-ID: 39780EEE.BBCB6406@technologist.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-generalpgsql-sql
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(at)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(at)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(at)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(at)alpinfo(dot)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/

******************************************************************************

Responses

pgsql-sql by date

Next:From: Volker PaulDate: 2000-07-21 09:10:50
Subject: Re: problem with view and case - please help
Previous:From: Jerome AletDate: 2000-07-21 08:49:09
Subject: Re: test

pgsql-bugs by date

Next:From: Volker PaulDate: 2000-07-21 09:10:50
Subject: Re: problem with view and case - please help
Previous:From: Tom LaneDate: 2000-07-21 06:18:59
Subject: Re: Damn bug!

pgsql-general by date

Next:From: Volker PaulDate: 2000-07-21 09:10:50
Subject: Re: problem with view and case - please help
Previous:From: Michaël FieyDate: 2000-07-21 08:44:10
Subject: Cursor in function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group