Updating multiple tables "simultaneously"

#1

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

using the tables below as an example:

CREATE TABLE library_def (id integer, name varchar(32));
CREATE TABLE library_books(bookid int,
libid int references library_def(id),
title varchar(32), author varchar(32));

I want to be able to write a function that allows me to INSERT a NEW
library definition (along with the books held in the library). Upon
failure, the transaction should rollback.

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

Note, in this example, I am only updating data accross two tables -
although it is possible that the data could be stored accross multiple
tables - I have used two tables here for simplicity, although i want to
be able to understand the general principles, so that I can modify any
solution provided, to cater for storing/retrieving data accross multiple
(i.e. N) tables.

On the flip side, when returning daat, I would like to return all the
rows (in this case library books) associated with a particular library
definition (i.e. location), as a single comma separated value string.

So the function for retrieving data will have the following prototype:

CREATE OR REPLACE FUNCTION fetch_library_data(lib_id integer) RETURNS
TEXT AS $$
BEGIN
-- Fetch library data using passed in lib_id
-- fetch the books (rows) associated with the lib_id
-- concatenate the library definition info
-- (id, name) as well as all of the associated books
-- (rows) into one long comma separated string and
-- return that string
RETURN "result goes here";
END;
$$ LANGUAGE plpgsql;

#2

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#3

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#4

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#5

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#6

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#7

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#8

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#9

begin Ronald Raygun schrieb:

Quote:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

Quote:

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
....
COMMIT;

If one of these steps failed -> the transaction failed completely.

Quote:

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

#10

Andreas Kretschmer wrote:

Quote:

begin Ronald Raygun schrieb:

I want to be able to update several tables "simultaneously". I have a
data entity that is stored accross multiple tables, in a normalised db
schema (4NF).

Use a TRANSACTION.

I would like to know how to wrap CRUD (Create, Retrieve, Update and
Delete functionality) in transactions, so that when part of the
operation fails, the entire transaction is 'rolled back'.

BEGIN;
Create ...
Select ...
Update ...
Delete ...
Truncate ...
...
COMMIT;

Does PGSQL have a switch statement (like C/C++ ?). If so, then I can
wrap all the functionality in one function, otherwise its better
(easier) to have seperate functions to Create/Retrieve Update and Delete.

if PGSQL has a switch statement, could you (or someone) please show how
I could use the swicth statement in my PGSQL function?

Quote:

If one of these steps failed -> the transaction failed completely.

The function should have the following 'prototype'

CREATE OR REPLACE FUNCTION insert_library_data(lib_id integer,
lib_name varchar,
books varchar
) RETURNS integer AS $$
BEGIN
-- // If anything goes wrong below, ROLLBACK
-- Insert the library data
-- parse the books string and get the individual rows
-- insert book rows
RETURN lib_id + 1;
END;
$$ LANGUAGE plpgsql;

A funktion runs in a own transaction, and yes, you can use a function.
But you should use SERIAL instead integer for the ID.

Thanks for the info. In the function above, the problem is that I do not

know how to parse the books argument so that I obtain rows, which can be
inserted into the library_books table. This is the part that I am
struggling with. I need to be able to write a function (say
'parse_string_to_rows') that does the ff:

1). accepts a comma seperated value string
2). parses the string into row(s) to be inserted into the library_books
table
3). returns the rows as (a table?) so that the function can be called
from my 'insert_library_data' function.

This is what I am struggling with, since I am not very familiar with PGSQL.

I would be grateful if you could show how I could write such a function,
and how I could then call the function from 'insert_library_data', and
rollback on an error.

Quote:

end
Andreas