X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6CA319FB363 for ; Mon, 31 Jul 2006 12:00:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 96328-03 for ; Mon, 31 Jul 2006 12:00:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 7B50C9FB366 for ; Mon, 31 Jul 2006 11:59:34 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so527796nfc for ; Mon, 31 Jul 2006 07:59:23 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=ONy+X/V6BE53vUf2HKkNymrGPp+FCyZZVPBpbfLwFLgaiN2hO96DyA7m5JNajaDfYmlhJwfB19M6JQz1LSD8SOHHJakOfzrSIYApkrmjZfjN8/oxGzGKzQ/+OReN+l0fhKXvc/K7ptTY0lThyuweY8M2PpnCoMdpjxThpHPtRYM= Received: by 10.78.132.12 with SMTP id f12mr181180hud; Mon, 31 Jul 2006 07:59:23 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Mon, 31 Jul 2006 07:59:22 -0700 (PDT) Message-ID: Date: Mon, 31 Jul 2006 09:59:22 -0500 From: "Aaron Bono" To: "John DeSoi" Subject: Re: Triggers using PL/pgSQL Cc: "Thusitha Kodikara" , pgsql-sql@postgresql.org In-Reply-To: <7F7548C8-249E-44E2-8205-6C055671DA19@pgedit.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_46738_25141344.1154357962997" References: <20060731041644.32247.qmail@web56610.mail.re3.yahoo.com> <7F7548C8-249E-44E2-8205-6C055671DA19@pgedit.com> X-Google-Sender-Auth: 75ce8c978cae9051 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.555 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: ** X-Archive-Number: 200607/377 X-Sequence-Number: 25951 ------=_Part_46738_25141344.1154357962997 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/31/06, John DeSoi wrote: > > Is it really necessary to build a SQL string and use execute? It > seems you could just issue the INSERT statement. I don't think so but there was some discussion a week or two ago about mixing variables and using execute. I am curious, does anyone know what the "best" approach is? Also, I did not address deletions. If you still need to delete from the table, you will need to get rid of the foreign key on the history table. You will also need to decide how the history table will reflect the recording of those deletions. I usually don't allow deletes on tables (unless absolutely necessary) and instead add start/end dates to the tables so rows can be marked as removed. Then I add a view that filters out the inactive rows - all applications use the views, they do not query the tables directly. This also allows you to "delete" rows at sometime in the future or make them appear in the future too. On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote: > > > 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; > > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > > -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_46738_25141344.1154357962997 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/31/06, John DeSoi <desoi@pgedit.com> wrote:
Is it really necessary to build a SQL string and use execute? It
seems you could just issue the INSERT statement.

I don't think so but there was some discussion a week or two ago about mixing variables and using execute.  I am curious, does anyone know what the "best" approach is?

Also, I did not address deletions.  If you still need to delete from the table, you will need to get rid of the foreign key on the history table.  You will also need to decide how the history table will reflect the recording of those deletions.

I usually don't allow deletes on tables (unless absolutely necessary) and instead add start/end dates to the tables so rows can be marked as removed.  Then I add a view that filters out the inactive rows - all applications use the views, they do not query the tables directly.  This also allows you to "delete" rows at sometime in the future or make them appear in the future too.

On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:

> 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;



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL




--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_46738_25141344.1154357962997--