From klodoma@ar-sd.net Mon Jul  18 12:22:42 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5F5CD52C9C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Jul 2005 09:22:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86614-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Jul 2005 12:22:38 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 7811152C8E
	for <pgsql-sql@postgresql.org>; Mon, 18 Jul 2005 09:22:36 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 69E6F1737D
	for <pgsql-sql@postgresql.org>; Mon, 18 Jul 2005 15:14:18 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 07025-07 for <pgsql-sql@postgresql.org>;
	Mon, 18 Jul 2005 15:14:16 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id C8451E6F2
	for <pgsql-sql@postgresql.org>; Mon, 18 Jul 2005 15:14:16 +0300 (EEST)
Message-ID: <00e001c58b93$ad30b130$0b00a8c0@forge>
From: "Andrei Bintintan" <klodoma@ar-sd.net>
To: <pgsql-sql@postgresql.org>
Subject: Create trigger for auto update function
Date: Mon, 18 Jul 2005 15:24:41 +0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00DD_01C58BAC.D134C760"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.167 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, HTML_60_70, HTML_MESSAGE, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200507/149
X-Sequence-Number: 22127

This is a multi-part message in MIME format.

------=_NextPart_000_00DD_01C58BAC.D134C760
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi to all,=20

I have a table:=20
create table hoy(
id serial,
pass varchar(40),=20
pass_md5 varchar(40);

Now, I want to write a trigger function that automatically updates the =
pass_md5 with the md5 function of the pass.=20

I tried this:

CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
    UPDATE hoy SET pass_md5=3Dmd5(pass) WHERE id=3D$1;
   SELECT 1;
$$ LANGUAGE SQL;

and=20

CREATE TRIGGER triger_users_pass_md5=20
 AFTER INSERT OR UPDATE
 ON hoy
    EXECUTE PROCEDURE update_pass(integer);=20


But it works not.=20
When I create the trigger it says that function does not exist.=20

I also tried with:


CREATE OR REPLACE FUNCTION user2(integer)RETURNS TRIGGER AS'
BEGIN
    UPDATE users SET pass_md5=3Dmd5(pass) WHERE id=3D$1;
 return NULL;
END
'language plpgsql;
.... the same


Need some help!!!!

Andy.
------=_NextPart_000_00DD_01C58BAC.D134C760
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2668" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi to all, </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have a table: </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>create table hoy(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>id serial,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>pass varchar(40), </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>pass_md5 varchar(40);</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Now, I want to write a trigger function =
that=20
automatically updates the pass_md5 with the md5 function of the pass.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I tried this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>CREATE FUNCTION update_pass(integer) =
RETURNS=20
integer AS $$<BR>&nbsp;&nbsp;&nbsp; UPDATE hoy SET pass_md5=3Dmd5(pass) =
WHERE=20
id=3D$1;<BR>&nbsp;&nbsp; SELECT 1;<BR>$$ LANGUAGE SQL;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>and </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>CREATE TRIGGER triger_users_pass_md5=20
<BR>&nbsp;AFTER INSERT OR =
UPDATE<BR>&nbsp;ON&nbsp;hoy<BR>&nbsp;&nbsp;&nbsp;=20
EXECUTE PROCEDURE update_pass(integer); </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>But it works not. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>When I create the trigger it says that =
function=20
does not exist. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I also tried with:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV><FONT face=3DArial =
size=3D2>
<DIV><BR>CREATE OR REPLACE FUNCTION user2(integer)RETURNS TRIGGER=20
AS'<BR>BEGIN<BR>&nbsp;&nbsp;&nbsp; UPDATE users SET pass_md5=3Dmd5(pass) =
WHERE=20
id=3D$1;<BR>&nbsp;return NULL;<BR>END<BR>'language plpgsql;</DIV>
<DIV>.... the same</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Need some help!!!!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Andy.</FONT></DIV></BODY></HTML>

------=_NextPart_000_00DD_01C58BAC.D134C760--




From lists@boutiquenumerique.com Mon Jul  18 13:21:54 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 097FC52AE1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Jul 2005 10:21:54 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 01546-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Jul 2005 13:21:50 +0000 (GMT)
Received: from boutiquenumerique.com (boutiquenumerique.com [82.67.9.10])
	by svr1.postgresql.org (Postfix) with ESMTP id A502B52AD5
	for <pgsql-sql@postgresql.org>; Mon, 18 Jul 2005 10:21:48 -0300 (ADT)
Received: (qmail 23033 invoked from network); 18 Jul 2005 15:22:27 +0200
Received: from unknown (HELO localhost) (boutiquenumerique-lists@192.168.0.4)
	by boutiquenumerique.com with SMTP; 18 Jul 2005 15:22:27 +0200
To: "Andrei Bintintan" <klodoma@ar-sd.net>, pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
Message-ID: <op.st312pikth1vuj@localhost>
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Date: Mon, 18 Jul 2005 15:21:51 +0200
In-Reply-To: <00e001c58b93$ad30b130$0b00a8c0@forge>
User-Agent: Opera M2/8.0 (Linux, build 1095)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200507/150
X-Sequence-Number: 22128



> CREATE TRIGGER triger_users_pass_md5
>  AFTER INSERT OR UPDATE
>  ON hoy
>     EXECUTE PROCEDURE update_pass(integer);

	Try : FOR EACH ROW EXECUTE



From daq@ugyvitelszolgaltato.hu Mon Jul  18 13:32:18 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7C1B852C92
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Jul 2005 10:32:18 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00593-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Jul 2005 13:32:12 +0000 (GMT)
Received: from wukifi.ugyvitelszolgaltato.hu (adsl215141.vnet.hu
	[62.77.215.141])
	by svr1.postgresql.org (Postfix) with SMTP id A19FB52AD5
	for <Pgsql-sql@postgresql.org>; Mon, 18 Jul 2005 10:32:10 -0300 (ADT)
Received: (qmail 28212 invoked from network); 18 Jul 2005 12:43:39 -0000
Received: from unknown (HELO x-eef90d131bdb4) (193.80.82.4)
	by 193.80.82.16 with SMTP; 18 Jul 2005 12:43:39 -0000
Date: Mon, 18 Jul 2005 15:32:08 +0200
From: daq <daq@ugyvitelszolgaltato.hu>
X-Mailer: The Bat! (v1.53d) Business
Reply-To: daq16 <daq@ugyvitelszolgaltato.hu>
X-Priority: 3 (Normal)
Message-ID: <1285936906.20050718153208@ugyvitelszolgaltato.hu>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: Pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
In-Reply-To: <00e001c58b93$ad30b130$0b00a8c0@forge>
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=2.073 tagged_above=0 required=5
	tests=FORGED_RCVD_HELO, RCVD_BY_IP, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL,
	UPPERCASE_25_50
X-Spam-Level: **
X-Archive-Number: 200507/151
X-Sequence-Number: 22129

Hello Andrei,

Monday, July 18, 2005, 2:24:41 PM, you wrote:

AB> Hi to all, 

AB> I have a table: 
AB> create table hoy(
AB> id serial,
AB> pass varchar(40), 
AB> pass_md5 varchar(40);

AB> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. 

AB> I tried this:

AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
AB>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
AB>    SELECT 1;
AB> $$ LANGUAGE SQL;

AB> and 

AB> CREATE TRIGGER triger_users_pass_md5 
AB>  AFTER INSERT OR UPDATE
AB>  ON hoy
AB>     EXECUTE PROCEDURE update_pass(integer); 

What will be the param of the trigger procedure?

Try this way:

 CREATE FUNCTION update_pass() RETURNS integer AS $$
     UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
    SELECT 1;
 $$ LANGUAGE SQL;

 CREATE TRIGGER triger_users_pass_md5
  AFTER INSERT OR UPDATE
  ON hoy FOR EACH ROW
     EXECUTE PROCEDURE update_pass;


DAQ




From frum@ar-sd.net Tue Jul  19 07:53:51 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 723F852987
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 04:53:51 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 57213-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 07:53:41 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 0D16352952
	for <Pgsql-sql@postgresql.org>; Tue, 19 Jul 2005 04:53:37 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 9FBD528BE7;
	Tue, 19 Jul 2005 10:45:11 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 14878-06; Tue, 19 Jul 2005 10:45:10 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id B71AD28BDD;
	Tue, 19 Jul 2005 10:45:10 +0300 (EEST)
Message-ID: <016401c58c37$43d52820$0b00a8c0@forge>
From: "Andy" <frum@ar-sd.net>
To: "daq16" <daq@ugyvitelszolgaltato.hu>
Cc: <Pgsql-sql@postgresql.org>
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
	<1285936906.20050718153208@ugyvitelszolgaltato.hu>
Subject: Re: Create trigger for auto update function
Date: Tue, 19 Jul 2005 10:55:41 +0300
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.569 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200507/157
X-Sequence-Number: 22135

> CREATE FUNCTION update_pass() RETURNS integer AS $$
>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
>    SELECT 1;
> $$ LANGUAGE SQL;
>
> CREATE TRIGGER triger_users_pass_md5
>  AFTER INSERT OR UPDATE
>  ON hoy FOR EACH ROW
>     EXECUTE PROCEDURE update_pass;

I understand the ideea, but don't know how to apply it.
I also receive the error that NEW must be definde as a rule.

Still... not working...




----- Original Message ----- 
From: "daq" <daq@ugyvitelszolgaltato.hu>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <Pgsql-sql@postgresql.org>
Sent: Monday, July 18, 2005 4:32 PM
Subject: Re: [SQL] Create trigger for auto update function


> Hello Andrei,
>
> Monday, July 18, 2005, 2:24:41 PM, you wrote:
>
> AB> Hi to all,
>
> AB> I have a table:
> AB> create table hoy(
> AB> id serial,
> AB> pass varchar(40),
> AB> pass_md5 varchar(40);
>
> AB> Now, I want to write a trigger function that automatically updates the 
> pass_md5 with the md5 function of the pass.
>
> AB> I tried this:
>
> AB> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
> AB>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
> AB>    SELECT 1;
> AB> $$ LANGUAGE SQL;
>
> AB> and
>
> AB> CREATE TRIGGER triger_users_pass_md5
> AB>  AFTER INSERT OR UPDATE
> AB>  ON hoy
> AB>     EXECUTE PROCEDURE update_pass(integer);
>
> What will be the param of the trigger procedure?
>
> Try this way:
>
> CREATE FUNCTION update_pass() RETURNS integer AS $$
>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
>    SELECT 1;
> $$ LANGUAGE SQL;
>
> CREATE TRIGGER triger_users_pass_md5
>  AFTER INSERT OR UPDATE
>  ON hoy FOR EACH ROW
>     EXECUTE PROCEDURE update_pass;
>
>
> DAQ
>
>
> ---------------------------(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
>
> 




From daq@ugyvitelszolgaltato.hu Tue Jul  19 08:11:01 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 626635295A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 05:11:01 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 62614-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 08:10:58 +0000 (GMT)
Received: from wukifi.ugyvitelszolgaltato.hu (adsl215141.vnet.hu
	[62.77.215.141])
	by svr1.postgresql.org (Postfix) with SMTP id 98F37528BE
	for <Pgsql-sql@postgresql.org>; Tue, 19 Jul 2005 05:10:56 -0300 (ADT)
Received: (qmail 10770 invoked from network); 19 Jul 2005 07:22:11 -0000
Received: from unknown (HELO x-eef90d131bdb4) (193.80.82.4)
	by 193.80.82.16 with SMTP; 19 Jul 2005 07:22:11 -0000
Date: Tue, 19 Jul 2005 10:10:51 +0200
From: daq <daq@ugyvitelszolgaltato.hu>
X-Mailer: The Bat! (v1.53d) Business
Reply-To: daq16 <daq@ugyvitelszolgaltato.hu>
X-Priority: 3 (Normal)
Message-ID: <687885328.20050719101051@ugyvitelszolgaltato.hu>
To: "Andy" <frum@ar-sd.net>
Cc: Pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
In-Reply-To: <016401c58c37$43d52820$0b00a8c0@forge>
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
	<1285936906.20050718153208@ugyvitelszolgaltato.hu>
	<016401c58c37$43d52820$0b00a8c0@forge>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=2.073 tagged_above=0 required=5
	tests=FORGED_RCVD_HELO, RCVD_BY_IP, RCVD_IN_NJABL_DUL, RCVD_IN_SORBS_DUL,
	UPPERCASE_25_50
X-Spam-Level: **
X-Archive-Number: 200507/158
X-Sequence-Number: 22136

Hello Andy,

Tuesday, July 19, 2005, 9:55:41 AM, you wrote:

>> CREATE FUNCTION update_pass() RETURNS integer AS $$
>>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
>>    SELECT 1;
>> $$ LANGUAGE SQL;
>>
>> CREATE TRIGGER triger_users_pass_md5
>>  AFTER INSERT OR UPDATE
>>  ON hoy FOR EACH ROW
>>     EXECUTE PROCEDURE update_pass;

A> I understand the ideea, but don't know how to apply it.
A> I also receive the error that NEW must be definde as a rule.

A> Still... not working...

Sorry! My fault. Trigger porcedure returns OPAQUE type.

 CREATE FUNCTION update_pass() RETURNS OPAQUE AS $$
     UPDATE hoy SET pass_md5=md5(pass) WHERE id=new.id;
    SELECT 1;
 $$ LANGUAGE SQL;



 DAQ




From dev@archonet.com Tue Jul  19 09:16:12 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2BF19528BE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 06:16:12 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76045-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 09:16:03 +0000 (GMT)
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by svr1.postgresql.org (Postfix) with ESMTP id 3616B52806
	for <pgsql-sql@postgresql.org>; Tue, 19 Jul 2005 06:16:00 -0300 (ADT)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id E034540DAD2; Tue, 19 Jul 2005 10:15:54 +0100 (BST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP id 0240315EE6;
	Tue, 19 Jul 2005 10:12:02 +0100 (BST)
Received: from mainbox.archonet.com ([127.0.0.1])
	by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 03747-01; Tue, 19 Jul 2005 10:11:57 +0100 (BST)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id C249515EDA;
	Tue, 19 Jul 2005 10:11:57 +0100 (BST)
Message-ID: <42DCC3DD.5000806@archonet.com>
Date: Tue, 19 Jul 2005 10:11:57 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create trigger for auto update function
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
In-Reply-To: <00e001c58b93$ad30b130$0b00a8c0@forge>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.159 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200507/159
X-Sequence-Number: 22137

Andrei Bintintan wrote:
> 
> Now, I want to write a trigger function that automatically updates the pass_md5 with the md5 function of the pass. 
> 
> I tried this:
> 
> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
>    SELECT 1;
> $$ LANGUAGE SQL;
> 
> and 
> 
> CREATE TRIGGER triger_users_pass_md5 
>  AFTER INSERT OR UPDATE
>  ON hoy
>     EXECUTE PROCEDURE update_pass(integer); 

The simplest way to do this is with a BEFORE trigger, and just modifying 
the NEW pseudo-record.


CREATE OR REPLACE FUNCTION maintain_pass_md5() RETURNS TRIGGER AS '
BEGIN
     NEW.pass_md5 = md5(NEW.pass);
     RETURN NEW;
END
' LANGUAGE plpgsql;

CREATE TRIGGER hoy_maintain_pass_md5
BEFORE INSERT OR UPDATE ON hoy
FOR EACH ROW EXECUTE PROCEDURE maintain_pass_md5();


Note that the function is defined to return type TRIGGER and that we 
return NEW. If we returned NULL, the row would be skipped by the current 
update statement. This means only one actual on-disk update takes place, 
and as far as everyone is concerned pass_md5 automagically updates itself.

If the md5() function was actually an operation that would take a long 
time, it might be worth checking whether pass has been changed:
   IF NEW.pass IS DISTINCT FROM OLD.pass THEN
     ...
   END IF
However, if you do this then you have to test TG_OP to see whether you 
are inserting or updating - insert ops don't have OLD defined.

HTH
--
   Richard Huxton
   Archonet Ltd



From frum@ar-sd.net Tue Jul  19 09:26:04 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3384552896
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 06:26:04 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78072-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Jul 2005 09:25:53 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 4EE605281E
	for <pgsql-sql@postgresql.org>; Tue, 19 Jul 2005 06:25:51 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 3844028BF5;
	Tue, 19 Jul 2005 12:17:25 +0300 (EEST)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 15980-05; Tue, 19 Jul 2005 12:17:23 +0300 (EEST)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id 47CEF28BDF;
	Tue, 19 Jul 2005 12:17:23 +0300 (EEST)
Message-ID: <01d301c58c44$25cddb80$0b00a8c0@forge>
From: "Andy" <frum@ar-sd.net>
To: "Richard Huxton" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
References: <00e001c58b93$ad30b130$0b00a8c0@forge>
	<42DCC3DD.5000806@archonet.com>
Subject: Re: Create trigger for auto update function
Date: Tue, 19 Jul 2005 12:27:54 +0300
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.342 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200507/161
X-Sequence-Number: 22139

Off topic
:) I think we posted in the same time :))

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 19, 2005 12:11 PM
Subject: Re: [SQL] Create trigger for auto update function


> Andrei Bintintan wrote:
>>
>> Now, I want to write a trigger function that automatically updates the 
>> pass_md5 with the md5 function of the pass. I tried this:
>>
>> CREATE FUNCTION update_pass(integer) RETURNS integer AS $$
>>     UPDATE hoy SET pass_md5=md5(pass) WHERE id=$1;
>>    SELECT 1;
>> $$ LANGUAGE SQL;
>>
>> and CREATE TRIGGER triger_users_pass_md5 AFTER INSERT OR UPDATE
>>  ON hoy
>>     EXECUTE PROCEDURE update_pass(integer);
>
> The simplest way to do this is with a BEFORE trigger, and just modifying 
> the NEW pseudo-record.
>
>
> CREATE OR REPLACE FUNCTION maintain_pass_md5() RETURNS TRIGGER AS '
> BEGIN
>     NEW.pass_md5 = md5(NEW.pass);
>     RETURN NEW;
> END
> ' LANGUAGE plpgsql;
>
> CREATE TRIGGER hoy_maintain_pass_md5
> BEFORE INSERT OR UPDATE ON hoy
> FOR EACH ROW EXECUTE PROCEDURE maintain_pass_md5();
>
>
> Note that the function is defined to return type TRIGGER and that we 
> return NEW. If we returned NULL, the row would be skipped by the current 
> update statement. This means only one actual on-disk update takes place, 
> and as far as everyone is concerned pass_md5 automagically updates itself.
>
> If the md5() function was actually an operation that would take a long 
> time, it might be worth checking whether pass has been changed:
>   IF NEW.pass IS DISTINCT FROM OLD.pass THEN
>     ...
>   END IF
> However, if you do this then you have to test TG_OP to see whether you are 
> inserting or updating - insert ops don't have OLD defined.
>
> HTH
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>