X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 48D5430F615 for ; Mon, 23 Jun 2003 16:45:19 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 12153-07 for ; Mon, 23 Jun 2003 13:45:08 -0300 (ADT) Received: from utahisp.com (mail.cyber-wire.com [66.239.12.3]) by svr1.postgresql.org (Postfix) with ESMTP id 2B7A030F659 for ; Mon, 23 Jun 2003 13:45:08 -0300 (ADT) Received: from chad [63.230.8.76] by utahisp.com (SMTPD32-8.00) id AE8BEBF601F0; Mon, 23 Jun 2003 10:44:59 -0600 Message-ID: <01d301c339a6$c227f9e0$32021aac@chad> From: "Chad Thompson" To: "Anagha Joshi" , References: Subject: Re: Urgent Help : Use of return from function/procedure. Date: Mon, 23 Jun 2003 10:44:46 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01D0_01C33974.76B922E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Declude-Sender: chad@weblinkservices.com [63.230.8.76] X-Declude-Spoolname: D2e8bebf601f00718.SMD X-Note: This E-mail was scanned by Declude JunkMail (www.declude.com) for spam. X-Spam-Status: No, hits=1.7 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN, HTML_FONT_COLOR_RED, HTML_MESSAGE, REFERENCES X-Spam-Level: ** X-Archive-Number: 200306/264 X-Sequence-Number: 14022 This is a multi-part message in MIME format. ------=_NextPart_000_01D0_01C33974.76B922E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Urgent Help : Use of return from function/procedure. ----- Original Message -----=20 From: Anagha Joshi=20 To: pgsql-sql@postgresql.org=20 Sent: Sunday, June 22, 2003 11:42 PM Subject: [SQL] Urgent Help : Use of return from function/procedure. Hi,=20 I'm new to postgres and using version 7.2.4=20 I've created a trigger and function which does the following:=20 trigger 'T' fires after insert on a spcific table takes place and= it executes function 'F'=20 Function 'F' returns the new record inserted by 'return new' statement.= =20 Now my question is:=20 How can I use this 'new' value in my client program? In this prgm., I wan= t to know which all values are inserted into the table. Help is appreciated.=20 Thx,=20 Anagha=20 Im not sure if this is what you are looking for. But I use postgres this w= ay to know which record I have sent. Its a very simple function and should= be self explanitory. I think the GET DIAGNOSTICS is the key for you in th= is case. =20 -- Function: public.return_mortgage_id(varchar, varchar) CREATE FUNCTION public.return_mortgage_id(varchar, varchar) RETURNS int8 AS= ' DECLARE oid1 INTEGER; retval integer; BEGIN insert into mortgage(contact_firstname, contact_lastname, date_submitted) values($1,$2, now()); GET DIAGNOSTICS oid1 =3D RESULT_OID; select id into retval from mortgage where oid =3D oid1; return retval; end;' LANGUAGE 'plpgsql' IMMUTABLE; Hope that helps Chad ------=_NextPart_000_01D0_01C33974.76B922E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Urgent Help : Use of return from function/procedure.</TI= TLE> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff><FONT size=3D1> <DIV><FONT face=3DArial size=3D2></FONT></FONT><FONT size=3D2><FONT face=3D= Arial><FONT=20 size=3D1><FONT size=3D2></FONT> </DIV></FONT></FONT></FONT> <BLOCKQUOTE dir=3Dltr=20 style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LE= FT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>Fro= m:</B>=20 <A title=3Dajoshi@nulinkinc.com href=3D"mailto:ajoshi@nulinkinc.com">Anag= ha=20 Joshi</A> </DIV> <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A title=3Dpgsql-sql@postgresq= l.org=20 href=3D"mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</A> </D= IV> <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Sunday, June 22, 2003 11:42= =20 PM</DIV> <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [SQL] Urgent Help : Use o= f=20 return from function/procedure.</DIV> <DIV><BR></DIV><!-- Converted from text/rtf format --> <P><FONT face=3DArial size=3D2>Hi,</FONT> <BR><FONT face=3DArial size=3D2= >I'm new to=20 postgres and using version 7.2.4</FONT> </P> <P><FONT face=3DArial size=3D2>I've created a trigger and function which = does the=20 following:</FONT> <BR>        <FONT=20 face=3DArial size=3D2>trigger 'T' fires after insert on a spcific table t= akes=20 place and it executes function 'F'</FONT> </P> <P><FONT face=3DArial size=3D2>Function 'F' returns the new record insert= ed by=20 'return new' statement.</FONT> </P> <P><FONT face=3DArial size=3D2>Now my question is:</FONT> <BR><FONT face= =3DArial=20 size=3D2>How can I use this 'new' value in my client program? In this prg= m., I=20 want to know which all values are inserted into the table.</FONT></P> <P><FONT face=3DArial size=3D2>Help is appreciated.</FONT> </P> <P><FONT face=3DArial size=3D2>Thx,</FONT> <BR><FONT face=3DArial=20 size=3D2>Anagha</FONT> </P></BLOCKQUOTE> <DIV dir=3Dltr><FONT face=3DArial size=3D2></FONT> </DIV> <DIV dir=3Dltr><FONT face=3DArial size=3D2>Im not sure if this is what you = are looking=20 for.  But I use postgres this way to know which record I have sent.&nb= sp;=20 Its a very simple function and should be self explanitory.  I think th= e GET=20 DIAGNOSTICS is the key for you in this case.</FONT></DIV> <DIV dir=3Dltr><FONT face=3DArial size=3D2></FONT> </DIV> <DIV dir=3Dltr><FONT size=3D1> </FONT> <DIV><FONT face=3DArial size=3D2>-- Function: public.return_mortgage_id(var= char,=20 varchar)</FONT></DIV><FONT color=3D#0000ff size=3D1> <DIV><FONT face=3DArial color=3D#000000 size=3D2>CREATE</FONT></FONT><FONT = face=3DArial=20 size=3D2> FUNCTION public.return_mortgage_id(varchar, varchar) RETURNS int8= AS=20 '</FONT><FONT size=3D1></DIV> <DIV><FONT face=3DArial size=3D2>DECLARE</FONT></DIV> <DIV><FONT face=3DArial size=3D2>oid1 INTEGER;</FONT></DIV> <DIV><FONT face=3DArial size=3D2>retval integer;</FONT></DIV> <DIV><FONT face=3DArial size=3D2>BEGIN</FONT></DIV></FONT><FONT color=3D#00= 00ff=20 size=3D1> <DIV><FONT face=3DArial color=3D#000000 size=3D2>insert</FONT></FONT><FONT = face=3DArial=20 size=3D2> into mortgage(contact_firstname, contact_lastname,=20 date_submitted)</FONT><FONT size=3D1></DIV> <DIV><FONT face=3DArial size=3D2>values($1,$2, now());</FONT></DIV> <DIV><FONT face=3DArial size=3D2>GET DIAGNOSTICS oid1 =3D=20 RESULT_OID;</FONT></DIV></FONT><FONT color=3D#0000ff size=3D1> <DIV><FONT face=3DArial color=3D#000000 size=3D2>select</FONT></FONT><FONT = face=3DArial=20 size=3D2> id into retval</FONT><FONT size=3D1></DIV></FONT><FONT color=3D#0= 08000=20 size=3D1> <DIV><FONT face=3DArial color=3D#000000 size=3D2>from</FONT></FONT><FONT si= ze=3D1><FONT=20 face=3DArial size=3D2> mortgage</FONT></DIV></FONT><FONT color=3D#008000 si= ze=3D1> <DIV><FONT face=3DArial color=3D#000000 size=3D2>where</FONT></FONT><FONT s= ize=3D1><FONT=20 face=3DArial size=3D2> oid =3D oid1;</FONT></DIV> <DIV><FONT face=3DArial size=3D2>return retval;</FONT></DIV> <DIV></FONT><FONT size=3D2><FONT face=3DArial>end;' <FONT=20 color=3D#ff0000>LANGUAGE</FONT><FONT size=3D1><FONT size=3D2> 'plpgsql'=20 IMMUTABLE;</FONT></FONT></FONT></FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Hope that helps</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Chad</FONT></DIV></DIV></BODY></HTML> ------=_NextPart_000_01D0_01C33974.76B922E0--