Re: [SQL] primary keys as TEXT
On 7/28/06, Manlio Perillo <[EMAIL PROTECTED]> wrote: Michael Glaesemann ha scritto:>> On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:>>> There can be performancs problems in having primary keys of type TEXT?>> What about having a primary key of 3 columns (all of type TEXT)? >> What defines a problem in terms of performance is heavily dependent on> your particular needs and requirements. What are your requirements? What> profiling have you done to see where your performance bottlenecks may be? >I still don't have done profiling.Simply in the first version of my schema I used serial keys but theresult is ugly and it force me to do a lot of joins. Ugly? Not sure what you mean by that. I do understand the problem with so many joins. I use views so that the joins are only delt with once (in the database) and then all my applications run off the views. That way, the applications use very simple queries. The views also allow me to change the table structure (column names, more table normalization, etc.) without having to make changes to the application. I am even getting ready to start using updatable views so my applications never touch the tables directly - it ads another layer of abstraction between the tables and the application. But that may be more complicated than you are ready for - I have yet to decide if it will be worth the effort but I won't know until I experiment with it more. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
[SQL] Triggers using PL/pgSQL
Hello,I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table). Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.Can someone please direct me to some such examples?Thanks and regards,-Thusitha
Re: [SQL] Triggers using PL/pgSQL
On 7/30/06, Thusitha Kodikara <[EMAIL PROTECTED]> wrote: Hello,I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table). Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL. Can someone please direct me to some such examples? How about this:CREATE TABLE my_table ( my_table_id BIGSERIAL NOT NULL, my_value VARCHAR(100) NOT NULL, CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id) );CREATE TABLE my_table_history ( my_table_id BIGINT NOT NULL, my_value VARCHAR(100) NOT NULL, create_dt TIMESTAMP NOT NULL, CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt) );CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF opaque AS'BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN execute ''INSERT INTO my_table_history ( '' || '' my_table_id, '' || '' my_value, '' || '' create_dt '' || '') VALUES ( '' || '' '' || NEW.my_table_id || '', '' || '' '' || NEW.my_value || '', '' || '' now() '' || '');'' ; RETURN NEW; END IF;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE my_table_history_fn();I tried it out and it works in version 8.1.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Triggers using PL/pgSQL
Hello,Thanks a lot Aaron for the very quick and simple example. I just checked it on 7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example.Regards,-ThusithaAaron Bono <[EMAIL PROTECTED]> wrote: On 7/30/06, Thusitha Kodikara <[EMAIL PROTECTED]> wrote: Hello,I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table). Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL. Can someone please direct me to some such examples? How about this:CREATE TABLE my_table ( my_table_id BIGSERIAL NOT NULL, my_value VARCHAR(100) NOT NULL, CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id) );CREATE TABLE my_table_history ( my_table_id BIGINT NOT NULL, my_value VARCHAR(100) NOT NULL, create_dt TIMESTAMP NOT NULL, CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt) );CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF opaque AS'BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN execute ''INSERT INTO my_table_history ( '' || '' my_table_id, '' || '' my_value, '' || '' create_dt '' || '') VALUES ( '' || '' '' || NEW.my_table_id || '', '' || '' '' || NEW.my_value || '', '' || '' now() '' || '');'' ; RETURN NEW; END IF;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE my_table_history_fn();I tried it out and it works in version 8.1.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Triggers using PL/pgSQL
No problem. I have been meaning to put the same code together for myself but have been putting it off. It gave me an excuse to stop procrastinating.On 7/31/06, Thusitha Kodikara <[EMAIL PROTECTED]> wrote: Hello,Thanks a lot Aaron for the very quick and simple example. I just checked it on 7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example. Aaron Bono < [EMAIL PROTECTED]> wrote: On 7/30/06, Thusitha Kodikara < [EMAIL PROTECTED]> wrote: Hello,I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table). Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL. Can someone please direct me to some such examples? How about this:CREATE TABLE my_table ( my_table_id BIGSERIAL NOT NULL, my_value VARCHAR(100) NOT NULL, CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id) );CREATE TABLE my_table_history ( my_table_id BIGINT NOT NULL, my_value VARCHAR(100) NOT NULL, create_dt TIMESTAMP NOT NULL, CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt) );CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF opaque AS'BEGIN -- if a trigger insert or update operation occurs IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN execute ''INSERT INTO my_table_history ( '' || '' my_table_id, '' || '' my_value, '' || '' create_dt '' || '') VALUES ( '' || '' '' || NEW.my_table_id || '', '' || '' '' || NEW.my_value || '', '' || '' now() '' || '');'' ; RETURN NEW; END IF;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT ON my_table FOR EACH ROW EXECUTE PROCEDURE my_table_history_fn();I tried it out and it works in version 8.1. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ==
