> ==========================================================================
> Cheers for your response. I agree, pg_dump/pg_dumpall works fine.
> It seems I was looking at this problem from the wrong direction.
>
> I now believe this to be a view storage issue and was hoping you could
> complete the following steps to confirm my findings. It should only take
> you 2 minutes to cut and paste the code.
>
> I would be very grateful for your help on this matter.
> Thank you in advance,
> Colin PRICE
>
> ============================================================================
> ==
> - Object : To confirm that pg stores ambiguious fieldnames when creating
> views
>
> [...]
>
> The output should be :
> ===================================================
> viewname |viewowner|definition
> ----------+---------+----------
> v_usertype|postgres |SELECT "description" AS "usertypedescription", "login"
> FROM
> "usertype", "useraccount" WHERE ("id" = "usertypeid") AND ("rowstatusid" =
> '0':
> :"int4");
> (1 row)
> ===================================================
> Note the rowstatusid fieldname has now become ambiguous since it is present
> within both tables. Therefore, when exported with pg_dump and re-loaded, the
> table 'v_usertype' is created but the rule fails.
>
> I would be grateful if the above could be confirmed or I could be pointed in
> the right direction.
>
Cannot confirm, Postgres doesn't store the rules action
querytree with ambiguous attributes. It is the function
pg_get_viewdef() located in src/utils/adt/ruleutils.c that
causes the problems. It's the little sister of
pg_get_ruledef() and both fail to recreate a legal
(unambiguous) statement from the rule action.
Postgres does not STORE your view or any rule as you typed it
in. The only thing that's stored is the nodeToString()
output for the querytree of your statement (the querytree is
the internal, binary output of the parser). It is the funny
string you see in pg_rewrite. There are ppl out who can read
that, but for those who can't (and for me to make life
easier) I wrote the above two functions. They aren't perfect
and I know other querytree constructs they would stumble
over.
I really would like to enhance them, but currently I've got
little spare time to do so. If you feel you could get
familiar enough with parsetree's to fix things in them,
you're welcome.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== [EMAIL PROTECTED] (Jan Wieck) #
