Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 635BC2E0073 for ; Fri, 4 Apr 2008 05:22:37 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75611-03 for ; Fri, 4 Apr 2008 05:22:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gslimes3.sigma.fr (mx.sigma.fr [194.50.39.191]) by developer.postgresql.org (Postfix) with ESMTP id E75082E004C for ; Fri, 4 Apr 2008 05:22:33 -0300 (ADT) Received: from gsw3mes2.groupesigma.fr (unknown [192.168.1.70]) by gslimes3.sigma.fr (Postfix) with ESMTP id ECFA236B2E for ; Fri, 4 Apr 2008 10:22:31 +0200 (CEST) Received: from marc.sigma.fr ([89.195.0.5]) by gsw3mes2.groupesigma.fr with Microsoft SMTPSVC(6.0.3790.3959); Fri, 4 Apr 2008 10:22:31 +0200 From: Marc Cousin Organization: Sigma Informatique To: pgsql-admin@postgresql.org Subject: cannot restore a view after a dump Date: Fri, 4 Apr 2008 10:22:31 +0200 User-Agent: KMail/1.9.9 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200804041022.31746.mcousin@sigma.fr> X-OriginalArrivalTime: 04 Apr 2008 08:22:31.0898 (UTC) FILETIME=[0739C3A0:01C8962D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200804/39 X-Sequence-Number: 28803 Hi, I'm having a strange problem : I created a view in a database, and I cannot restore it after a pg_dump. I'm creating this view : CREATE VIEW vj_icsi_integration_winaudit_isiparc AS SELECT DISTINCT objet.c_barre , resume.computer , resume.site , (resume.loctime)::DATE AS "DATEINVENLOG", resume.operatingsystem AS "UC-SE" , systeme.servicepack AS "UC-SEVERS" , processeurs.nbprocessor AS "UC-CPUNB" , resume.processordescription AS "UC-CPUTYP" , (((processeurs.speedregistry)::DOUBLE PRECISION / (1024)::DOUBLE PRECISION) / (1024)::DOUBLE PRECISION) AS "UC-CPUVIT" , disques.disknumber AS "UC-DDNB" , ROUND(((((resume.totalharddrive / 1024) / 1024) / 1024))::DOUBLE PRECISION) AS "UC-DDESP" , CASE WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber = '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::INTEGER END AS "UC-SIMM" , ((resume.totalmemory / 1024) / 1024) AS "UC-RAM" , reseau.ipaddress[1] AS "UC-CRIP" , reseau.ipaddress[2] AS "UC-CRIP_1", reseau.ipaddress[3] AS "UC-CRIP_2", CASE WHEN (reseau.dhcpipaddress IS NOT NULL) THEN 'oui'::text ELSE 'non'::text END AS "UC-DHCP" FROM ((((((winaudit.winaudit_resum_systeme resume JOIN winaudit.winaudit_systeme_exploitation systeme ON ((resume.computer = systeme.computer))) JOIN (SELECT winaudit_reseau.computer , group_array(winaudit_reseau.ipaddress) AS ipaddress, group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress FROM winaudit.winaudit_reseau GROUP BY winaudit_reseau.computer ) reseau ON ((resume.computer = reseau.computer))) JOIN (SELECT winaudit_processeurs.computer , MAX(winaudit_processeurs.processornumber) AS processornumber, COUNT(winaudit_processeurs.processornumber) AS nbprocessor , MAX(winaudit_processeurs.speedregistry) AS speedregistry FROM winaudit.winaudit_processeurs GROUP BY winaudit_processeurs.computer ) processeurs ON ((resume.computer = processeurs.computer))) JOIN (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit_management_systeme_memoire.devicenumber)::text) AS devicenumber FROM winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_systeme_memoire.computer ) memoire ON ((resume.computer = memoire.computer))) JOIN (SELECT winaudit_disques_physiques.computer, COUNT(winaudit_disques_physiques.disknumber) AS disknumber FROM winaudit.winaudit_disques_physiques GROUP BY winaudit_disques_physiques.computer ) disques ON ((resume.computer = disques.computer))) LEFT JOIN isilog.objet ON ((resume.computername = (objet.i_ob_nom)::text))) ORDER BY objet.c_barre , resume.computer , resume.site , (resume.loctime)::DATE , resume.operatingsystem , systeme.servicepack , processeurs.nbprocessor , resume.processordescription , (((processeurs.speedregistry)::DOUBLE PRECISION / (1024)::DOUBLE PRECISION) / (1024)::DOUBLE PRECISION), disques.disknumber , ROUND(((((resume.totalharddrive / 1024) / 1024) / 1024))::DOUBLE PRECISION) , CASE WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber = '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::INTEGER END , ((resume.totalmemory / 1024) / 1024), reseau.ipaddress[1] , reseau.ipaddress[2] , reseau.ipaddress[3] , CASE WHEN (reseau.dhcpipaddress IS NOT NULL) THEN 'oui'::text ELSE 'non'::text END; (I know it's ugly, but the source database is ugly too :( ) Here's the result from \d on this view : SELECT DISTINCT objet.c_barre, resume.computer, resume.site, resume.loctime::date AS "DATEINVENLOG", resume.operatingsystem AS "UC-SE", systeme.servicepack AS "UC-SEVERS", processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP", processeurs.speedregistry::double precision / 1024::double precision / 1024::double precision AS "UC-CPUVIT", disques.disknumber AS "UC-DDNB", round((resume.totalharddrive / 1024 / 1024 / 1024)::double precision) AS "UC-DDESP", CASE WHEN memoire.devicenumber = '{1,2,3,4,5,6}'::text[] THEN 6 WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5 WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4 WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3 WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2 WHEN memoire.devicenumber = '{1}'::text[] THEN 1 ELSE NULL::integer END AS "UC-SIMM", resume.totalmemory / 1024 / 1024 AS "UC-RAM", reseau.ipaddress[1] AS "UC-CRIP", reseau.ipaddress[2] AS "UC-CRIP_1", reseau.ipaddress[3] AS "UC-CRIP_2", CASE WHEN reseau.dhcpipaddress IS NOT NULL THEN 'oui'::text ELSE 'non'::text END AS "UC-DHCP" FROM winaudit_resum_systeme resume JOIN winaudit_systeme_exploitation systeme ON resume.computer = systeme.computer JOIN ( SELECT winaudit_reseau.computer, group_array(winaudit_reseau.ipaddress) AS ipaddress, group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress FROM winaudit_reseau GROUP BY winaudit_reseau.computer) reseau ON resume.computer = reseau.computer JOIN ( SELECT winaudit_processeurs.computer, max(winaudit_processeurs.processornumber) AS processornumber, count(winaudit_processeurs.processornumber) AS nbprocessor, max(winaudit_processeurs.speedregistry) AS speedregistry FROM winaudit_processeurs GROUP BY winaudit_processeurs.computer) processeurs ON resume.computer = processeurs.computer JOIN ( SELECT winaudit_management_systeme_memoire.computer, group_array(winaudit_management_systeme_memoire.devicenumber::text) AS devicenumber FROM winaudit_management_systeme_memoire GROUP BY winaudit_management_systeme_memoire.computer) memoire ON resume.computer = memoire.computer JOIN ( SELECT winaudit_disques_physiques.computer, count(winaudit_disques_physiques.disknumber) AS disknumber FROM winaudit_disques_physiques GROUP BY winaudit_disques_physiques.computer) disques ON resume.computer = disques.computer LEFT JOIN objet ON resume.computername = objet.i_ob_nom::text ORDER BY objet.c_barre, resume.computer, resume.site, resume.loctime::date, resume.operatingsystem, systeme.servicepack, processeurs.nbprocessor, resume.processordescription, processeurs.speedregistry::double precision / 1024::double precision / 1024::double precision, disques.disknumber, round((resume.totalharddrive / 1024 / 1024 / 1024)::double precision), CASE WHEN memoire.devicenumber = '{1,2,3,4,5,6}'::text[] THEN 6 WHEN memoire.devicenumber = '{1,2,3,4,5}'::text[] THEN 5 WHEN memoire.devicenumber = '{1,2,3,4}'::text[] THEN 4 WHEN memoire.devicenumber = '{1,2,3}'::text[] THEN 3 WHEN memoire.devicenumber = '{1,2}'::text[] THEN 2 WHEN memoire.devicenumber = '{1}'::text[] THEN 1 ELSE NULL::integer END, resume.totalmemory / 1024 / 1024, reseau.ipaddress[1], reseau.ipaddress[2], reseau.ipaddress[3], CASE WHEN reseau.dhcpipaddress IS NOT NULL THEN 'oui'::text ELSE 'non'::text END; Notice postgreSQL added an order by ... pg_dump gives me this : CREATE VIEW vj_icsi_integration_winaudit_isiparc AS SELECT DISTINCT objet.c_barre, resume.computer, resume.site, (resume.loctime)::date AS "DATEINVENLOG", resume.operatingsystem AS "UC-SE", systeme.servicepack AS "UC-SEVERS", processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP", (((processeurs.speedregistry)::double precision / (1024)::double precision) / (1024)::double precision) AS "UC-CPUVIT", disques.disknumber AS "UC-DDNB", round(((((resume.totalharddrive / 1024) / 1024) / 1024))::double precision) AS "UC-DDESP", CASE WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber = '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END AS "UC-SIMM", ((resume.totalmemory / 1024) / 1024) AS "UC-RAM", reseau.ipaddress[1] AS "UC-CRIP", reseau.ipaddress[2] AS "UC-CRIP_1", reseau.ipaddress[3] AS "UC-CRIP_2", CASE WHEN (reseau.dhcpipaddress IS NOT NULL) THEN 'oui'::text ELSE 'non'::text END AS "UC-DHCP" FROM ((((((winaudit.winaudit_resum_systeme resume JOIN winaudit.winaudit_systeme_exploitation systeme ON ((resume.computer = systeme.computer))) JOIN (SELECT winaudit_reseau.computer, group_array(winaudit_reseau.ipaddress) AS ipaddress, group_concat_virgule(winaudit_reseau.dhcpipaddress) AS dhcpipaddress FROM winaudit.winaudit_reseau GROUP BY winaudit_reseau.computer) reseau ON ((resume.computer = reseau.computer))) JOIN (SELECT winaudit_processeurs.computer, max(winaudit_processeurs.processornumber) AS processornumber, count(winaudit_processeurs.processornumber) AS nbprocessor, max(winaudit_processeurs.speedregistry) AS speedregistry FROM winaudit.winaudit_processeurs GROUP BY winaudit_processeurs.computer) processeurs ON ((resume.computer = processeurs.computer))) JOIN (SELECT winaudit_management_systeme_memoire.computer, group_array((winaudit_management_systeme_memoire.devicenumber)::text) AS devicenumber FROM winaudit.winaudit_management_systeme_memoire GROUP BY winaudit_management_systeme_memoire.computer) memoire ON ((resume.computer = memoire.computer))) JOIN (SELECT winaudit_disques_physiques.computer, count(winaudit_disques_physiques.disknumber) AS disknumber FROM winaudit.winaudit_disques_physiques GROUP BY winaudit_disques_physiques.computer) disques ON ((resume.computer = disques.computer))) LEFT JOIN isilog.objet ON ((resume.computername = (objet.i_ob_nom)::text))) ORDER BY objet.c_barre, resume.computer, resume.site, (resume.loctime)::date, resume.operatingsystem, systeme.servicepack, processeurs.nbprocessor, resume.processordescription, (((processeurs.speedregistry)::double precision / (1024)::double precision) / (1024)::double precision), disques.disknumber, round(((((resume.totalharddrive / 1024) / 1024) / 1024))::double precision), CASE WHEN (memoire.devicenumber = '{1,2,3,4,5,6}'::text[]) THEN 6 WHEN (memoire.devicenumber = '{1,2,3,4,5}'::text[]) THEN 5 WHEN (memoire.devicenumber = '{1,2,3,4}'::text[]) THEN 4 WHEN (memoire.devicenumber = '{1,2,3}'::text[]) THEN 3 WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END, ((resume.totalmemory / 1024) / 1024), reseau.ipaddress[1], reseau.ipaddress[2], reseau.ipaddress[3], CASE WHEN (reseau.dhcpipaddress IS NOT NULL) THEN 'oui'::text ELSE 'non'::text END; And when I try to restore it, here's what I've got : ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list I'm a bit lost on this ... Can anyone provide some help ? Thanks a lot ...