Re: pg_dump, pg_restore. - Mailing list pgsql-general

<!--
body {
  margin: 5px 5px 5px 5px;
  background-color: #ffffff;
}
/* ---------- Text Styles ---------- */
hr { color: #000000}
body, table /* Normal text */
{
 font-size: 10pt;
 font-family: 'Tahoma';
 font-style: normal;
 font-weight: normal;
 color: #000000;
 text-decoration: none;
}
span.rvts1 /* Heading */
{
 font-family: 'Arial';
 font-weight: bold;
 color: #0000ff;
}
span.rvts2 /* Subheading */
{
 font-family: 'Arial';
 font-weight: bold;
 color: #000080;
}
span.rvts3 /* Keywords */
{
 font-family: 'Arial';
 font-style: italic;
 color: #800000;
}
a.rvts4, span.rvts4 /* Jump 1 */
{
 font-family: 'Arial';
 color: #008000;
 text-decoration: underline;
}
a.rvts5, span.rvts5 /* Jump 2 */
{
 font-family: 'Arial';
 color: #008000;
 text-decoration: underline;
}
span.rvts6
{
 font-size: 9pt;
}
span.rvts7
{
 font-size: 9pt;
 font-weight: bold;
}
span.rvts8
{
 font-size: 9pt;
 font-weight: bold;
 color: #0000ff;
}
span.rvts9
{
 font-size: 9pt;
 font-weight: bold;
 color: #0000ff;
 text-decoration: underline;
}
span.rvts10
{
 font-size: 9pt;
 font-family: 'courier new';
 color: #800000;
}
a.rvts11, span.rvts11
{
 font-size: 9pt;
 font-family: 'courier new';
 color: #0000ff;
 text-decoration: underline;
}
span.rvts12
{
 font-size: 9pt;
 font-family: 'courier new';
 color: #800000;
}
span.rvts13
{
 font-size: 9pt;
 font-family: 'courier new';
 color: #800080;
}
span.rvts14
{
 font-size: 8pt;
 font-family: 'arial';
 font-style: italic;
 color: #808080;
}
/* ---------- Para Styles ---------- */
p,ul,ol /* Paragraph Style */
{
 text-align: left;
 text-indent: 0px;
 padding: 0px 0px 0px 0px;
 margin: 0px 0px 0px 0px;
}
.rvps1 /* Centered */
{
 text-align: center;
}
-->



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 named moja_schema. 
I have table in scheme named tb_tabulka.
I have function named my_function() in scheme moja_schema.
I want set default value for field id_kotuc to my_function().
It is not work, because my_function() is in scheme named moja_schema, not in scheme public.

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, because my_function() is not in public scheme. It is
in moja_schema scheme.
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
notexist". 

Thank you for your help.



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

TL> "Emil J." <EmilJ@pyton.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
inserttwo 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
theintended recipient of this e-mail, you are hereby on notice of this status. Any disclosure, copying, distribution,
disseminationor publication of the information contained therein is strictly prohibited, unless you have been permitted
theretoby the sender, and might be a breach of confidence. If you are not the intended recipient, please return this
e-mailimmediately to the sender and then delete this message from your system. The sender is not liable for the proper
transmissionof this information nor for any delay in its receipt.<SPAN lang=EN-GB style="FONT-SIZE: 8pt; FONT! 
 -FAMILY: Arial; mso-ansi-language: EN-GB">