cannot restore a view after a dump - Mailing list pgsql-admin

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.operatingsystemAS "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.operatingsystemAS "UC-SE", systeme.servicepack AS "UC-SEVERS",  
processeurs.nbprocessor AS "UC-CPUNB", resume.processordescription AS "UC-CPUTYP",
(((processeurs.speedregistry)::doubleprecision / (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_systemeresume 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_physiquesGROUP 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 ...