Hi,
This is the version I used to run the following commands
select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 64-bit
(1 row)
Run these commands
CREATE TABLE price (id INT PRIMARY KEY, active BOOLEAN NOT NULL, price
NUMERIC);
insert into price values (1,false,42), (10,false,100), (11,true,17.99);
create view v2 as select price.*::price from price;
select * from v2;
price
--------------
(1,f,42)
(10,f,100)
(11,t,17.99)
(3 rows)
\d+ v2;
View "public.v2"
Column | Type | Modifiers | Storage | Description
--------+-------+-----------+----------+-------------
price | price | | extended |
View definition:
SELECT price AS price
FROM price;
Note the output from the view, also note the "Type" in view defination.
Now take dump of this database.
./pg_dump --file=/home/user_name/d.sql --format=p --inserts -p 4444 test
The dump file is attached with the mail. (d.sql)
Now lets restore this dump.
./createdb test2 -p 4444
./psql -p 4444 -f /home/user_name/d.sql test2
./psql test2 -p 4444
psql (9.2devel)
Type "help" for help.
test2=# select * from v2;
price
-------
42
100
17.99
(3 rows)
test2=# \d+ v2
View "public.v2"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
price | numeric | | main |
View definition:
SELECT price.price
FROM price;
In the database test2 the view was not restored correctly.
The output of the view as well as the Type in its defination is wrong.
The cause of the problem is as follows
The notation "relation.*" represents a whole-row reference.
While parsing a whole-row reference is transformed into a Var with varno
set to the correct range table entry,
and varattno == 0 to signal that it references the whole tuple. (For
reference see comments of function makeWholeRowVar)
While deparsing we need to take care of this case.
The attached patch provides deparsing of a whole-row reference.
A whole row reference will be deparsed either into alias.*::relation or
relation.*::relation depending on alias
--
Abbas
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3beed37..272d1a5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -4007,7 +4007,47 @@ get_variable(Var *var, int levelsup, bool showstar, deparse_context *context)
}
if (attnum == InvalidAttrNumber)
+ {
attname = NULL;
+ /*
+ * The notation "relation.*" represents a whole-row reference.
+ * While parsing a whole-row reference is transformed into
+ * a Var with varno set to the correct range table entry,
+ * and varattno == 0 to signal that it references the whole tuple.
+ * For reference see comments of function makeWholeRowVar
+ * While deparsing we need to take care of this case
+ * This block of code is deparsing a whole-row reference.
+ * A whole row reference will be deparsed either into alias.*::relation
+ * or relation.*::relation depending on alias
+ * Explicit typecasting to relation is needed because
+ * input of anonymous composite types is not implemented
+ */
+
+ if (rte->relid != InvalidOid && refname && var->varattno == 0 &&
+ var->varno >= 1 && var->varno <= list_length(dpns->rtable))
+ {
+ char *rel_name;
+ List *schemalist;
+
+ schemalist = NULL;
+ if (schemaname)
+ list_make1(schemaname);
+
+ /* This relation name is required for explicit type casting later */
+ rel_name = generate_relation_name(rte->relid, schemalist);
+
+ /* Add name space qualification if required */
+ if (schemaname)
+ appendStringInfo(buf, "%s.", quote_identifier(schemaname));
+ appendStringInfoString(buf, quote_identifier(refname));
+ appendStringInfoString(buf, ".*::");
+ appendStringInfoString(buf, quote_identifier(rel_name));
+
+ pfree(rel_name);
+
+ return attname;
+ }
+ }
else
attname = get_rte_attribute_name(rte, attnum);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 0e7177e..cb39881 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -387,9 +387,9 @@ SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table
(1 row)
SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
- pg_get_triggerdef
---------------------------------------------------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE trigger_func('modified_any')
+ pg_get_triggerdef
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.*::main_table IS DISTINCT FROM new.*::main_table) EXECUTE PROCEDURE trigger_func('modified_any')
(1 row)
DROP TRIGGER modified_a ON main_table;
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: price; Type: TABLE; Schema: public; Owner: edb; Tablespace:
--
CREATE TABLE price (
id integer NOT NULL,
active boolean NOT NULL,
price numeric
);
ALTER TABLE public.price OWNER TO edb;
--
-- Name: v2; Type: VIEW; Schema: public; Owner: edb
--
CREATE VIEW v2 AS
SELECT price AS price FROM price;
ALTER TABLE public.v2 OWNER TO edb;
--
-- Data for Name: price; Type: TABLE DATA; Schema: public; Owner: edb
--
INSERT INTO price VALUES (1, false, 42);
INSERT INTO price VALUES (10, false, 100);
INSERT INTO price VALUES (11, true, 17.99);
--
-- Name: price_pkey; Type: CONSTRAINT; Schema: public; Owner: edb; Tablespace:
--
ALTER TABLE ONLY price
ADD CONSTRAINT price_pkey PRIMARY KEY (id);
--
-- Name: public; Type: ACL; Schema: -; Owner: edb
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM edb;
GRANT ALL ON SCHEMA public TO edb;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
