Thread: pg_dump(all) and views, broke
Hi all CREATE RULE "_RETmessages" AS ON SELECT TO "messages" DO INSTEAD SELECT "title", "mess", "iurl", "lurl", "posted", "fname", "lname", "email", "uid", "ppid", "pid", "bid" FROM "post", "users" WHERE "uid" = "uid"; ERROR: Column uid is ambiguous It left off the table names from 'WHERE "uid" = "uid";' I do not profess to understand all this rule stuff, but I don't understand why when I create a view using 'create view ....', then why does pg_dump need to create the view as a table, then later create a rule to make the table into a view? Why not just dump a 'create view ....' command instead? Not that it matters, as long as it works, just seems confusing is all. Thanks, have a great day. Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.4 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
>
> Hi all
>
> CREATE RULE "_RETmessages" AS ON SELECT TO "messages" DO INSTEAD SELECT
> "title", "mess", "iurl", "lurl", "posted", "fname", "lname", "email",
> "uid", "ppid", "pid", "bid" FROM "post", "users" WHERE "uid" = "uid";
> ERROR: Column uid is ambiguous
>
> It left off the table names from 'WHERE "uid" = "uid";'
>
> I do not profess to understand all this rule stuff, but I don't understand
> why when I create a view using 'create view ....', then why does pg_dump
> need to create the view as a table, then later create a rule to make the
> table into a view? Why not just dump a 'create view ....' command
> instead? Not that it matters, as long as it works, just seems confusing
> is all.
Creating a table first and turn it later into view by CREATE
RULE is just for simplification of pg_dump. It does not need
to make a difference between those rules that are handmade
production rules and those that came in due to CREATE VIEW.
But the above is a bug and I'll fix it soon.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Hi Jan
OK, thanks for your reply.
A followup, I noticed that if the table names have been aliased, then it
puts in the alias name, it's only when not aliased that it leaves out the
names
Here is a much more complicated one that works fine, but uses aliased
table names:
CREATE RULE "_RETkeywcatlist" AS ON SELECT TO "keywcatlist" DO INSTEAD
SELECT "l"."title", "l"."discription", "l"."url", "l"."lanme",
"l"."fname", "l"."email",
"l"."ent_date", "l"."mod_date", "l"."approved", "l"."item_id",
"k"."keyword", "c"."category" FROM "listings" "l", "keywords" "k",
"keyw2list" "k2l", "categories" "c", "cat2list" "c2l" WHERE
((("l"."item_id" = "k2l"."item_id") AND ("k"."keyw_id" = "k2l"."keyw_id"))
AND ("l"."item_id" = "c2l"."item_id")) AND ("c"."category" =
"c2l"."category");
I know the prefixes could have been left off of unique names, but put them
on for clarity in the future. ... or for confusion, however it works out:)
Thanks
Terry Mackintosh <terry@terrym.com> http://www.terrym.com
sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.
Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.4
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!
Hi Jan Also, it leaves off the table names in the select clause, as well as the where clause. I just noticed that. (when it still did not work after hand editing the where clause :) I hope I'm being helpful, and not being a pest. Have a great day, and thanks Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.4 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
>
> Hi Jan
>
> Also, it leaves off the table names in the select clause, as well as the
> where clause. I just noticed that. (when it still did not work after hand
> editing the where clause :)
>
> I hope I'm being helpful, and not being a pest.
Bug reports are a kind of helpful pest :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #