Postgres Pro
Downloads
Home   >   mailing lists

Thread: Database version control

Database version control

From
Alex du Plessis
Date:
Hello list,

Is there any way to do version control on a database from one
application version to another?

IE how do I do an upgrade (or even know that I have to do an upgrade) of
the database when I have a new application release.

Re: Database version control

From
Shane Ambler
Date:
Alex du Plessis wrote:
> Hello list,
>
> Is there any way to do version control on a database from one
> application version to another?
>
> IE how do I do an upgrade (or even know that I have to do an upgrade) of
> the database when I have a new application release.
>

Provided you have a copy of the db structure that you released initially
you will want to start with a current dump (no data)

You can diff these two files to find any differences.

You may also want to have a look at pgdiff and apgdiff - both are
located at sourceforge.



--

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz

Re: Database version control

From
Emil Oppeln-Bronikowski
Date:
Dnia 19-11-2007, Pn o godzinie 22:46 +0200, Alex du Plessis pisze:

> Is there any way to do version control on a database from one
> application version to another?

    I just have database structure near code and I manage it with
Subversion. But my favorite way to keep changes visible is to have basic
structure and keep all ALTER queries in files. Then I have
00BasicStruct.sql 01PasswordField-20071212.sql and so on, After I'm
close to freeze, I build clean structure and dump it into Subversion
repo

Using Execute with Dynamic Raise Commands

From
Robert Bernabe
Date:
Hi All,
   I've been trying to create a debugging function that would receive a
tablename and a list of columns and then the function would display all
the contents of the table using the RAISE command.

CREATE OR REPLACE FUNCTION usp_PG_DUMPTEMPTABLE(varchar(100), text[])
   RETURNS void AS
 $BODY$
 DECLARE
    _temptable ALIAS FOR $1;
    _temparray alias for $2;

    _i            integer;
    _max             integer;
    _tempstring        varchar(2000);

 BEGIN

    _tempstring := 'RAISE INFO''';
    for _i in 1 ..array_upper(_temparray, 1)
    loop
        _tempstring := _tempstring || _temparray[_i] || '   ' ;
    end loop;
    _tempstring := _tempstring || ''';';

    raise info'%', _tempstring;
    execute _tempstring;
    raise info '---';


     RETURN;
 END;
 $BODY$
   LANGUAGE 'plpgsql';


for some reason, the EXECUTE command issues an error at the start fo the
RAISE command...help?

Error from PG Admin III

ERROR:  syntax error at or near "RAISE"
LINE 1: RAISE DEBUG 'SKUID   CatID   ';
        ^
QUERY:  RAISE DEBUG 'SKUID   CatID   ';
CONTEXT:  PL/pgSQL function "usp_pg_dumptemptable" line 42 at EXECUTE
statement


Regards

Re: Using Execute with Dynamic Raise Commands

From
Tom Lane
Date:
Robert Bernabe <rbernabe@sandmansystems.com> writes:
>    I've been trying to create a debugging function that would receive a
> tablename and a list of columns and then the function would display all
> the contents of the table using the RAISE command.

There's no such thing as a "dynamic raise command" --- EXECUTE is for
executing regular SQL statements, not plpgsql things.  You hardly need
anything dynamic in your example anyway, seeing that you've built up
the string you want to display just fine.

            regards, tom lane