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 ...
--
Sent via pgsql-admin mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
