Re: pg_dump, pg_restore.

First, i apologize, my english skills is very, very poor.

-------

i have many tables in many schemas with default value like that: DEFAULT schema.function(...).

i need backup all databases and i need restore it.

IF YOU WANT UNDERSTAND ME, YOU NEED DO THIS:

YOU TRY RESTORE _DB_TEST_.PGB FILE TO DB_TEST DATABASE.

AFTER RESTORE, YOU TRY TO INSERT TWO OR MORE ROWS/RECORDS IN TABLE TB_TABULKA.

IF FIELD MOJA_SCHEMA.TB_TABULKA.ID_KOTUC CONTAIN VALUES LIKE KT00000001, KT00000002, ...3, ...4, ....

ALL IS OK,IF NOT - MAY BE WRONG RESTORED ?

i have many tables with fields that have function as default value.

Another side of same problem:

I have scheme namedmoja_schema.

I have table in scheme namedtb_tabulka.

I have function namedmy_function()in schememoja_schema.

I want set default value for fieldid_kotuctomy_function().

It is not work, becausemy_function()is in scheme namedmoja_schema, not in schemepublic.

ALTER TABLE "moja_schema"."tb_tabulka"

ALTER COLUMN "id_kotuc" SET DEFAULT my_function();

ERROR: function my_function() does not exist

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Yes, it is true, becausemy_function()is not inpublicscheme. It is inmoja_schemascheme.

It require full path to function:

ALTER TABLE "moja_schema"."tb_tabulka"

ALTER COLUMN "id_kotuc" SET DEFAULT moja_schema.my_function();

It works fine.

Problem is, when i do backup database and restore database.

Full path is cut off - table can not generate default value for field id_kotuc, because: "function my_function() does not exist".

Thank you for your help.

Thursday, February 14, 2008, 5:03:31 AM, si napisal:

TL "Emil J." EmilJ(at)pyton(dot)sk writes:

Before pg_dump, default value is: ... DEFAULT moja_schema.fn_sq_id_kotuc() ...

After pg_restore, default value is: ... DEFAULT fn_sq_id_kotuc() ...

The name of the scheme is missing, it is cut off.

I need first variant of default value (with name of the schema), because second variant raise exception if I insert two or more records.

TL No, you don't need that. The two versions you claim are different are

TL in fact exactly the same thing. Please show us the actual problem

TL you're having, not an uninformed guess as to the cause.

TL regards, tom lane

--

This e-mail as well as any files transmitted with it is confidential and may well contain information which is legally privileged. It is intended solely for the use of the individual or the entity to whom it is addressed. If you are not the intended recipient of this e-mail, you are hereby on notice of this status. Any disclosure, copying, distribution, dissemination or publication of the information contained therein is strictly prohibited, unless you have been permitted thereto by the sender, and might be a breach of confidence. If you are not the intended recipient, please return this e-mail immediately to the sender and then delete this message from your system. The sender is not liable for the proper transmission of this information nor for any delay in its receipt.