Re: pgadmin3 bug

Thanks Marc, fix applied and tested.

Regards, dave.

-----Original Message-----
From: Marc Persuy [mailto:marc(dot)persuy(at)wanadoo(dot)fr]
Sent: 19 August 2003 14:16
To: pgadmin-support(at)postgresql(dot)org
Subject: [pgadmin-support] pgadmin3 bug





Hello: here is a bug in pgadmin3.

I use a postgresQL 7.3.2 database, upon which I added some comments on view definition AND view attributes.

for instance :

---------------------------------------------------------
CREATE TABLE regions_sys (
num_region integer DEFAULT nextval('num_region_seq'::text) NOT NULL,
owner_tag integer,
num_world_set integer,
cd_pays character varying(2),
cd_zone character varying(8),
is_zone boolean,
);

CREATE OR REPLACE VIEW public.regions AS
SELECT sys.num_region, sys.num_world_set, sys.owner_tag, sys.cd_pays, sys.cd_zone, priv.valide, sys.is_zone, FROM (regions_sys sys
LEFT JOIN regions_priv_allies priv ON ((sys.num_region = priv.num_region )))
WHERE ((sys.owner_tag = ANY (
SELECT allies.owner_tag
FROM allies );

COMMENT ON VIEW public.regions IS 'Chacune des zones ainsi définies appartient à un « espace de travail » ou « world_set » qui peut être soit global (1 seul espace de travail global - ou longue distance - par compte client t2g) soit local (espaces locaux affinant une zone globale).';

COMMENT ON COLUMN regions.num_world_set IS 'Cf. world_set.num_world_set : espace de travail auquel appartient la région';
COMMENT ON COLUMN regions.cd_pays IS 'Valorisé pour toutes les régions incluses dans un pays';
COMMENT ON COLUMN regions.cd_zone IS 'Libellé court de la zone';
COMMENT ON COLUMN regions.is_zone IS 'Vrai si la région est définie en tant que zone dans un espace de travail global ou local';
-------------------------------------------
IN this case, pgadmin3 shows 5 entries for the view "regions" in the main tree in view list. 1 for each comment defined on the view, whithout filtering comments defined at the attribute level and not at the view level.


Suggested change :

in file pgView.cpp, lines 113 to 124 :
wxT("SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, ")
wxT("pg_get_viewdef(c.oid") + collection->GetDatabase()->GetPrettyOption() + wxT(") AS definition\n")
wxT(" FROM pg_class c\n")
wxT(" LEFT OUTER JOIN pg_description des ON des.objoid=c.oid\n")
wxT(" WHERE ((c.relhasrules AND (EXISTS (\n")
wxT(" SELECT r.rulename FROM pg_rewrite r\n")
wxT(" WHERE ((r.ev_class = c.oid)\n")
wxT(" AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))\n")
wxT(" AND relnamespace = ") + collection->GetSchema()->GetOidStr() + wxT("\n")
+ restriction
+ wxT(" ORDER BY relname"));

should be replaced by :

wxT("SELECT c.oid, c.relname, pg_get_userbyid(c.relowner) AS viewowner, c.relacl, description, ")
wxT("pg_get_viewdef(c.oid") + collection->GetDatabase()->GetPrettyOption() + wxT(") AS definition\n")
wxT(" FROM pg_class c\n")
wxT(" LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid and des.objsubid=0)\n")
wxT(" WHERE ((c.relhasrules AND (EXISTS (\n")
wxT(" SELECT r.rulename FROM pg_rewrite r\n")
wxT(" WHERE ((r.ev_class = c.oid)\n")
wxT(" AND (bpchar(r.ev_type) = '1'::bpchar)) ))) OR (c.relkind = 'v'::char))\n")
wxT(" AND relnamespace = ") + collection->GetSchema()->GetOidStr() + wxT("\n")
+ restriction
+ wxT(" ORDER BY relname"));


But, I should recognize that, despite being supported by PostgresQL 7.3.2, I'm not sure that this view condition is proper sql.

If not, to maintain the left outer join from view def with view description, one should define an intermediate view on table pg_description.


Similarly, commenting view columns is supported by current product, but in fact an undocumented feature.