problem with view and case - please help - Mailing list pgsql-sql
From | Ange Michel POZZO |
---|---|
Subject | problem with view and case - please help |
Date | |
Msg-id | 39780EEE.BBCB6406@technologist.com Whole thread Raw |
Responses |
Re: [BUGS] problem with view and case - please help
(JanWieck@t-online.de (Jan Wieck))
Re: [BUGS] problem with view and case - please help (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-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@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/ ******************************************************************************