Обсуждение: Problem with view upgrading from 7.2 to 7.3

Hi,
I am upgrading Postgres from version 7.2.1 to 7.3.2
I use pg_dump (the 7.3.2 version) to dump the old database
into the file. I use psql to load it into the new server.

Generally it seem to work, but it breaks while attempting to load
one view definition. Server complains about nonexistant function -
varchar(int), as far as I remember.
It appears that some casting is no longer supported.
This is the fragment from the dump file that results in error:

CREATE VIEW v_note_links AS
  SELECT note_links_aux.nl_id AS id, note_links_aux.nl_obj_url AS obj_url,
  ((('sync:///Note/SyncNotes/'::"varchar" || note_links_aux.nt_note_name)
  || '/'::"varchar") || "varchar"(note_links_aux.nl_note_id)) AS note_url,
  note_links_aux.nt_note_name AS note_name, note_links_aux.nl_note_id AS
  note_id, note_links_aux.nt_obj_name AS obj_name, note_links_aux.nl_obj_id
  AS obj_id FROM note_links_aux;
------------------------------------------
There's no problem if I try to manually create the view in the way it was
created in the old database:
CREATE VIEW v_note_links AS
SELECT  nl_id               AS id
    ,nl_obj_url             AS obj_url
    ,'sync:///Note/SyncNotes/' || nt_note_name || '/'
     || nl_note_id AS note_url
    ,nt_note_name           AS note_name
    ,nl_note_id             AS note_id
    ,nt_obj_name            AS obj_name
    ,nl_obj_id              AS obj_id
FROM note_links_aux ;
-----------------------------------------------
I would appreciate any help on how to handle this problem.

Mike.

Michael Brusser <michael@synchronicity.com> writes:
> Generally it seem to work, but it breaks while attempting to load
> one view definition. Server complains about nonexistant function -
> varchar(int), as far as I remember.

Yeah, we removed the int->varchar cast because it was redundant with
int->text, and caused operator resolution failures in some cases.

> There's no problem if I try to manually create the view in the way it was
> created in the old database:

Re-enter the view, then.  The || expression will come out as text
instead of varchar; if you really want the view column to look like
it's a varchar you can do

     ('sync:///Note/SyncNotes/' || nt_note_name || '/'
      || nl_note_id)::varchar AS note_url

            regards, tom lane