I have a view definition that creates a view on both Postgres 7 and
Postgres 8. The view definition works OK in that it does not return an
error and the resulting view seems to operate correctly on both versions of
Postgres.
However Pgadmin 1.2.0 works fine with the Postgres 7 database containing
the view but fails with the same database created on Postgres 8.0.0.rc4.
The cause of the pgadmin failure appears to be a call to
pg_get_viewdef(c.oid, true) used by pgadmin to populate its view display.
This function applied to my view works correctly on Postgres 7 but
generates the error Bogus Varno: 3
in Postgres 8.
The original query definition (as generated by Pgadmin 1.2.0 from Postgres
7.4.2) causing the problem was:
CREATE OR REPLACE VIEW full_product_view AS
SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_,
( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text
FROM rspper
WHERE rspper.rspperid = slimprdmgrrspperid) AS
_prd_slimprdmgrrspperid_d,
prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_,
actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS
_slimprdnmgnnmeid_gennme_catnmeclssid_
FROM prd
JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid;
AFAICT the pg_get_viewdef function called by pgadmin objects to the scalar
subselect in the above view defintion. Rewriting my view query to include
the table qualification on the scalar sub select cures the problem with
pg_get_viewdef(c.oid, true).
The rewritten query definition is:
CREATE OR REPLACE VIEW full_product_view AS
SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_,
( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text
FROM rspper
WHERE rspper.rspperid = prd.slimprdmgrrspperid) AS
_prd_slimprdmgrrspperid_d,
prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_,
actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS
_slimprdnmgnnmeid_gennme_catnmeclssid_
FROM prd
JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid;
I don't know if this behaviour is expected.
Regards
Laurie
:.________________
CONFIDENTIALITY : This e-mail and any attachments are confidential
and may be privileged. If you are not a named recipient, please notify
the sender immediately and do not disclose the contents to another
person, use it for any purpose or store or copy the information in any
medium.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
