Re: [SQL] Moving around in a SQL database
Hello Willem, you are writing an edit mask? Then do the following. Feed the mask with the data from the record. If the user changes anything and presses the save button, then issue an UPDATE WHERE ID= to the database. Always remember: you are working with a database, not with a dbf-file. If your transaction fails because of power outage or anything else, postgresql will revert to the consistent state before that transaction. If you want to batch update the records, then do a BEGIN TRANSACTION SELECT FOR UPDATE. After that collect the data you want to change. Then issue your update statements. After that do a COMMIT TRANSACTION. If anything fails between BEGIN and COMMIT TRANSACTION all changes will be reverted to the state before the transaction. With kind regards Mit freundlichen Grüssen Florian Reiser -- http://www.ra-bc.de RA Unternehmensberatung Führen durch präzise Daten "WillemF" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Thank you very much for your reply, Florian. It's probably the best > that one can do. I have two complicating factors. Firstly, I need to > udate the records one at a time and, secondly, I am accessing the table > via a Java front end through JDBC. Loading all the data into memory > opens the possibility for data corruption since, if anything goes wrong > on the computer or power supply, there is no guarantee that all updates > will have been written to file. Writing each update to file upon > accessing each record again requires working with the whole table when > manipulating a single record. The use of cursors is an elegant solution > with the drawback that updates cannot be performed (that is, if I > unerstand the documentation correctly!). Thank you very much for your > time. Kind regards. Willem. > ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Porting application with rules and triggers from PG 7.4.x to 8.1.3
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!
I'm currently porting a rather complex application from
PostgreSQL 7.4.x to 8.1.3 and I'm having problems with
changed semantics of the trigger functionality.
The problem is quite complex and I'm trying to describe
the functionality involved as detailled as necessary.
If you have any question please feel free to ask!
My application is using lots of temporal tables(*).
Each temporal table has two additional columns "from" and "to"
which store the time period where a single row was "valid".
In this concept, a row which still contains valid data has a
value of 'infinity' in its "to" column.
Example:
CREATE TABLE mwdb.t_ug
(
id serial NOT NULL,
from mwdb.d_pit NOT NULL DEFAULT now(),
to mwdb.d_pit NOT NULL DEFAULT ('infinity'::d_pit)::d_pit,
name mwdb.d_name NOT NULL,
code mwdb.d_code NOT NULL,
kommentar mwdb.d_comment
);
For each temporal table there is also a "current view" which
shows all rows from the temporal table where "to" = 'infinity'
(i.e. all rows which contain data which is still valid at the
current PIT)
Example:
CREATE OR REPLACE VIEW mwdb.vc_ug AS
SELECT t_ug.id,
t_ug.name,
t_ug.code,
t_ug.kommentar
FROM mwdb.t_ug
WHERE (t_ug.to = 'infinity');
Most database operations in the application are done against
these "current views".
I'm using rules, triggers and several PL/pgSQL functions
to enforce constraints like primary keys and foreign keys,
because the "standard" constraints of a relational database
do not work for temporal tables due to implicit temporal
semantics.
Each "current view" has attached "INSERT", "UPDATE" and
"DELETE" rules so that the user can work with the current
view "tables" as with any normal, non-temporal table. All
modifications to the current view are recorded in the
temporal table by use of PL/pgSQL functions which are
called by these rules.
There is never a row changed or deleted in a temporal table,
every change to any row in the current view is recorded as
a new row in the temporal table.
As an example I'm showing the function "func_ug_update"
which is called from the UPDATE rule on view "vc_ug",
which is the "current view" for temporal table "t_ug":
CREATE OR REPLACE RULE rule_ug_update AS
ON UPDATE TO mwdb.vc_ug DO INSTEAD
SELECT mwdb.func_ug_update(old.id::mwdb.d_rid, new.name, new.code,
new.kommentar) AS func_ug_update;
CREATE OR REPLACE FUNCTION mwdb.func_ug_update(mwdb.d_rid, mwdb.d_name,
mwdb.d_code, mwdb.d_comment)
RETURNS int4 AS $$
DECLARE
old_id ALIAS FOR $1;
new_name ALIAS FOR $2;
new_code ALIAS FOR $3;
new_kommentar ALIAS FOR $4;
retval integer;
now_pit d_pit;
BEGIN
now_pit := (now())::d_pit;
UPDATE t_ug SET
to=now_pit
WHERE
id = old_id AND
to = 'infinity';
IF FOUND=true
THEN
INSERT INTO t_ug (id, from, name, code, kommentar)
VALUES (old_id, now_pit, new_name, new_code, new_kommentar);
END IF;
GET DIAGNOSTICS retval = ROW_COUNT;
return retval;
END;
$$ LANGUAGE plpgsql;
As you can see this function "closes" the row from t_ug which
has "to" set to 'infinity' by storing the current PIT into
the "to" column. Then it inserts a new row with the updated
data and sets the "from" column to the current PIT (the insert
operation implicitly sets the "to" column to 'infinity')
This implements a "sequenced valid-time, closed-open interval"
temporal table concept.
So far, so good. But there is also a foreign key relationship
between table "t_ug" and table "t_pns". Table "t_pns" also is
a temporal table and contains a column "ug" which references
column "id" in table "t_ug" as a foreign key.
CREATE TABLE mwdb.t_pns
(
id serial NOT NULL,
from mwdb.d_pit NOT NULL DEFAULT now(),
to mwdb.d_pit NOT NULL DEFAULT ('infinity'::d_pit)::d_pit,
ug mwdb.d_rid NOT NULL,
name mwdb.d_name NOT NULL,
code mwdb.d_code NOT NULL,
kommentar mwdb.d_comment
);
The "temporal table foreign key constraint" says: For each row
in table "t_pns" with column "ug" set to N and column "to" set
to 'infinity' there must always be one row in table "t_ug" with
column "id" set to N and column "to" set to 'infinity'
This constraint is enforced by a special trigger function which
is attached to table "t_ug" as follows:
CREATE TRIGGER trigger_fk_ug_pns
AFTER UPDATE OR DELETE
ON mwdb.t_ug
FOR EACH ROW
EXECUTE PROCEDURE mwdb.func_fk_temporal_trigger('t_pns', 'ug', 't_ug', 'id');
The trigger function itself is rather complicated because it
is generic for all temporal tables but it implements the
"temporal table foreign key constraint" as mentioned above.
The function looks as follows:
CREATE OR REPLACE FUNCTION mwdb.func_fk_temporal_trigger()
RETURNS trigger AS
$$
DECLARE
referer_tab text;
referer_col text;
referenced_tab text;
referenced_col text;
stmt varchar(4000);
result record;
BEGIN
referer_tab := TG_ARGV[0];
referer_col := TG_ARGV[1];
referenced_tab := TG_ARGV[2];
referenced_co
Re: [SQL] Porting application with rules and triggers from PG 7.4.x to 8.1.3
Andreas Haumer <[EMAIL PROTECTED]> writes: > How can I get the functionality of an "deferred AFTER trigger" > again with PostgreSQL 8? Use CREATE CONSTRAINT TRIGGER. The manual is fairly negative about this but I don't actually foresee it going away any time soon. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] find all tables with a specific column name?
Is there a reasonable way to extract a list of all tables which contain a specific column name from the system views on 8.1? For instance, I might want to enumerate all tables with a column named last_modified. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] find all tables with a specific column name?
On Fri, Apr 21, 2006 at 09:29:33 -0700, Jeff Frost <[EMAIL PROTECTED]> wrote: > Is there a reasonable way to extract a list of all tables which contain a > specific column name from the system views on 8.1? > > For instance, I might want to enumerate all tables with a column named > last_modified. Take a look at: http://developer.postgresql.org/docs/postgres/infoschema-columns.html and http://developer.postgresql.org/docs/postgres/infoschema-schema.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] find all tables with a specific column name?
On Fri, 21 Apr 2006, Bruno Wolff III wrote: On Fri, Apr 21, 2006 at 09:29:33 -0700, Jeff Frost <[EMAIL PROTECTED]> wrote: Is there a reasonable way to extract a list of all tables which contain a specific column name from the system views on 8.1? For instance, I might want to enumerate all tables with a column named last_modified. Take a look at: http://developer.postgresql.org/docs/postgres/infoschema-columns.html Thanks Bruno! It appears I can simply do this: select table_name from information_schema.columns where column_name = 'last_modified'; -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] find all tables with a specific column name?
I've found it useful to explore the information_schema schema by doing: set search_path=information_schema; -- Lets just look at the system tables. \d -- Show me all the tables. ... (40 rows) then select * from some-likely-looking-table limit 20; In this case, I quickly found a table called "columns", so you can do: select table_name from information_schema.columns where column_name='last_modified'; Of course you could be a wuss and actually read the documentation ;-) http://www.postgresql.org/docs/8.1/interactive/infoschema-columns.html -- George Young On Fri, 21 Apr 2006 09:29:33 -0700 (PDT) Jeff Frost <[EMAIL PROTECTED]> wrote: > Is there a reasonable way to extract a list of all tables which contain a > specific column name from the system views on 8.1? > > For instance, I might want to enumerate all tables with a column named > last_modified. > > -- > Jeff Frost, Owner <[EMAIL PROTECTED]> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Porting application with rules and triggers from PG 7.4.x
Tom Lane wrote: > Andreas Haumer <[EMAIL PROTECTED]> writes: > > How can I get the functionality of an "deferred AFTER trigger" > > again with PostgreSQL 8? > > Use CREATE CONSTRAINT TRIGGER. The manual is fairly negative about this > but I don't actually foresee it going away any time soon. Do we need to update the manual? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
