Skip to content

Commit b259e45

Browse files
vadim4ovadim4o
committed
Dumping sequence relations as 'CREATE SEQUENCE ...'.
1 parent a15158b commit b259e45

File tree

2 files changed

+103
-7
lines changed

2 files changed

+103
-7
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 101 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@
2020
*
2121
*
2222
* IDENTIFICATION
23-
* $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.25 1997/03/01 15:24:51 momjian Exp $
23+
* $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.26 1997/04/02 04:17:21 vadim Exp $
2424
*
2525
* Modifications - 6/10/96 - dave@bensoft.com - version 1.13.dhb
2626
*
@@ -61,6 +61,8 @@
6161

6262
#include "pg_dump.h"
6363

64+
static void dumpSequence (FILE* fout, TableInfo tbinfo);
65+
6466
extern char *optarg;
6567
extern int optind, opterr;
6668

@@ -71,6 +73,8 @@ FILE *g_fout; /* the script file */
7173
PGconn *g_conn; /* the database connection */
7274
int dumpData; /* dump data using proper insert strings */
7375
int attrNames; /* put attr names into insert strings */
76+
int schemaOnly;
77+
int dataOnly;
7478

7579
char g_opaque_type[10]; /* name for the opaque type */
7680

@@ -346,7 +350,23 @@ dumpClasses(const TableInfo tblinfo[], const int numTables, FILE *fout,
346350
if (isViewRule(tblinfo[i].relname))
347351
continue;
348352

349-
if (!onlytable || (!strcmp(classname,onlytable))) {
353+
if (!onlytable || (!strcmp(classname,onlytable)))
354+
{
355+
if ( tblinfo[i].sequence )
356+
{
357+
if ( dataOnly ) /* i.e. SCHEMA didn't dumped */
358+
{
359+
if ( g_verbose )
360+
fprintf (stderr, "%s dumping out schema of sequence %s %s\n",
361+
g_comment_start, classname, g_comment_end);
362+
dumpSequence (fout, tblinfo[i]);
363+
}
364+
else if ( g_verbose )
365+
fprintf (stderr, "%s contents of sequence '%s' dumped in schema %s\n",
366+
g_comment_start, classname, g_comment_end);
367+
continue;
368+
}
369+
350370
if (g_verbose)
351371
fprintf(stderr, "%s dumping out the contents of Table %s %s\n",
352372
g_comment_start, classname, g_comment_end);
@@ -372,8 +392,6 @@ main(int argc, char** argv)
372392
const char* progname;
373393
const char* filename;
374394
const char* dbname;
375-
int schemaOnly;
376-
int dataOnly;
377395
const char *pghost = NULL;
378396
const char *pgport = NULL;
379397
const char *tablename;
@@ -905,6 +923,7 @@ getTables(int *numTables)
905923
int i_oid;
906924
int i_relname;
907925
int i_relarch;
926+
int i_relkind;
908927

909928
/* find all the user-defined tables (no indices and no catalogs),
910929
ordering by oid is important so that we always process the parent
@@ -921,8 +940,8 @@ getTables(int *numTables)
921940
PQclear(res);
922941

923942
sprintf(query,
924-
"SELECT oid, relname, relarch from pg_class "
925-
"where relkind = 'r' and relname !~ '^pg_' "
943+
"SELECT oid, relname, relarch, relkind from pg_class "
944+
"where (relkind = 'r' or relkind = 'S') and relname !~ '^pg_' "
926945
"and relname !~ '^Xinv' order by oid;");
927946

928947
res = PQexec(g_conn, query);
@@ -941,11 +960,13 @@ getTables(int *numTables)
941960
i_oid = PQfnumber(res,"oid");
942961
i_relname = PQfnumber(res,"relname");
943962
i_relarch = PQfnumber(res,"relarch");
963+
i_relkind = PQfnumber(res,"relkind");
944964

945965
for (i=0;i<ntups;i++) {
946966
tblinfo[i].oid = strdup(PQgetvalue(res,i,i_oid));
947967
tblinfo[i].relname = strdup(PQgetvalue(res,i,i_relname));
948968
tblinfo[i].relarch = strdup(PQgetvalue(res,i,i_relarch));
969+
tblinfo[i].sequence = (strcmp (PQgetvalue(res,i,i_relkind), "S") == 0);
949970
}
950971

951972
PQclear(res);
@@ -1041,6 +1062,9 @@ getTableAttrs(TableInfo* tblinfo, int numTables)
10411062
/* skip archive tables */
10421063
if (isArchiveName(tblinfo[i].relname))
10431064
continue;
1065+
1066+
if ( tblinfo[i].sequence )
1067+
continue;
10441068

10451069
/* find all the user attributes and their types*/
10461070
/* we must read the attribute names in attribute number order! */
@@ -1500,6 +1524,12 @@ void dumpTables(FILE* fout, TableInfo *tblinfo, int numTables,
15001524
/* skip archive names*/
15011525
if (isArchiveName(tblinfo[i].relname))
15021526
continue;
1527+
1528+
if ( tblinfo[i].sequence )
1529+
{
1530+
dumpSequence (fout, tblinfo[i]);
1531+
continue;
1532+
}
15031533

15041534
parentRels = tblinfo[i].parentRels;
15051535
numParents = tblinfo[i].numParents;
@@ -1814,3 +1844,68 @@ checkForQuote(const char* s)
18141844
return result;
18151845

18161846
}
1847+
1848+
1849+
static void dumpSequence (FILE* fout, TableInfo tbinfo)
1850+
{
1851+
PGresult *res;
1852+
int4 last, incby, maxv, minv, cache;
1853+
char cycled, called, *t;
1854+
char query[MAXQUERYLEN];
1855+
1856+
sprintf (query,
1857+
"SELECT sequence_name, last_value, increment_by, max_value, "
1858+
"min_value, cache_value, is_cycled, is_called from %s;",
1859+
tbinfo.relname);
1860+
1861+
res = PQexec (g_conn, query);
1862+
if ( !res || PQresultStatus(res) != PGRES_TUPLES_OK )
1863+
{
1864+
fprintf (stderr,"dumpSequence(%s): SELECT failed\n", tbinfo.relname);
1865+
exit_nicely (g_conn);
1866+
}
1867+
1868+
if ( PQntuples (res) != 1 )
1869+
{
1870+
fprintf (stderr,"dumpSequence(%s): %d (!= 1) tuples returned by SELECT\n",
1871+
tbinfo.relname, PQntuples(res));
1872+
exit_nicely (g_conn);
1873+
}
1874+
1875+
if ( strcmp (PQgetvalue (res,0,0), tbinfo.relname) != 0 )
1876+
{
1877+
fprintf (stderr, "dumpSequence(%s): different sequence name "
1878+
"returned by SELECT: %s\n",
1879+
tbinfo.relname, PQgetvalue (res,0,0));
1880+
exit_nicely (g_conn);
1881+
}
1882+
1883+
1884+
last = atoi (PQgetvalue (res,0,1));
1885+
incby = atoi (PQgetvalue (res,0,2));
1886+
maxv = atoi (PQgetvalue (res,0,3));
1887+
minv = atoi (PQgetvalue (res,0,4));
1888+
cache = atoi (PQgetvalue (res,0,5));
1889+
t = PQgetvalue (res,0,6);
1890+
cycled = *t;
1891+
t = PQgetvalue (res,0,7);
1892+
called = *t;
1893+
1894+
PQclear (res);
1895+
1896+
sprintf (query,
1897+
"CREATE SEQUENCE %s start %d increment %d maxvalue %d "
1898+
"minvalue %d cache %d %s;\n",
1899+
tbinfo.relname, last, incby, maxv, minv, cache,
1900+
(cycled == 't') ? "cycle" : "");
1901+
1902+
fputs (query, fout);
1903+
1904+
if ( called == 'f' )
1905+
return; /* nothing to do more */
1906+
1907+
sprintf (query, "SELECT nextval ('%s');\n", tbinfo.relname);
1908+
fputs (query, fout);
1909+
1910+
}
1911+

‎src/bin/pg_dump/pg_dump.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
* Copyright (c) 1994, Regents of the University of California
77
*
8-
* $Id: pg_dump.h,v 1.10 1997/01/07 00:04:19 scrappy Exp $
8+
* $Id: pg_dump.h,v 1.11 1997/04/02 04:17:27 vadim Exp $
99
*
1010
* Modifications - 6/12/96 - dave@bensoft.com - version 1.13.dhb.2
1111
*
@@ -55,6 +55,7 @@ typedef struct _tableInfo {
5555
char *oid;
5656
char *relname;
5757
char *relarch;
58+
bool sequence;
5859
int numatts; /* number of attributes */
5960
int *inhAttrs; /* an array of flags, one for each attribute
6061
if the value is 1, then this attribute is

0 commit comments

Comments
 (0)