Обсуждение: 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) #