Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Jun 3, 2016 at 3:16 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 06/03/2016 12:23 AM, Patrick Baker wrote:

    -- Creating the backup table with the essential data
    INSERT INTO table2 (row.note_id, row.size, row.file_id, row.full_path)

    .....

    Still not seeing what the JOIN to table3 t3 gets you?

    Any way the function works.


I changed the function to use row.note_id, row.size, etc... think it's
more intelligent that way! :)


 Is there any way to create another function to restore the data back?

I am sure there is, but it will probably be more difficult then copying that data in the first place. From your previous function there seems to be lot of moving parts. Unwinding those tables and any other data that is dependent on those tables could be a chore.

​Adrian,​

​I don't think its that bad.  All that was done is updating a bytea (or text...) field to NULL after saving the original contents elsewhere.  Restoring should be as simple as

UPDATE tbl SET data = "">

FROM archive_tbl

WHERE tbl.file_id = archive_tbl.file_id

AND tbl.file_id = <user input>;

Updating the main migrated flag and cleaning up extraneous entries in the archive would be simple.  No rows in the main tables were added or removed.

Patrick,

You already wrote the archive function; you should be capable of at least attempting to write its inverse.

If you are wondering how to pass the value 123414 in:

select function_data_1_restore(123414);

That would depend on the client.  In psql you'd just type it in.  In Java you probably do something like:

stmt = conn.prepareStatement("SELECT function_data_1_restore(?)");

stmt.setInteger(1, new Integer(123414));

stmt.execute();

David J.