From skarthi98@hotmail.com Tue Apr  3 17:42:32 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id A5FA89FB9B3;
	Tue,  3 Apr 2007 14:42:32 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 04273-01; Tue,  3 Apr 2007 14:42:23 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from BAY0-OMC3-S10.bay0.hotmail.com (bay0-omc3-s10.bay0.hotmail.com
	[65.54.246.210])
	by postgresql.org (Postfix) with ESMTP id CAD599FBAB1;
	Tue,  3 Apr 2007 14:42:23 -0300 (ADT)
Received: from BAY131-W7 ([65.55.136.42]) by BAY0-OMC3-S10.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.2668);
	Tue, 3 Apr 2007 10:42:22 -0700
Message-ID: <BAY131-W7F67EF97B4EA5FFB34746B0670@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_bd515516-9c52-4d50-96b8-7382af19be82_"
X-Originating-IP: [67.116.31.14]
From: Karthikeyan Sundaram <skarthi98@hotmail.com>
To: <pgsql-admin@postgresql.org>, <pgsql-sql@postgresql.org>
Subject: plpgsql function question
Date: Tue, 3 Apr 2007 10:42:22 -0700
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 03 Apr 2007 17:42:22.0721 (UTC)
	FILETIME=[6F505710:01C77617]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.564 tagged_above=0 required=5 tests=AWL=-0.738,
	BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708,
	HTML_50_60=0.134, HTML_MESSAGE=0.001, SPF_PASS=-0.001
X-Spam-Level: 
X-Archive-Number: 200704/24
X-Sequence-Number: 25088

--_bd515516-9c52-4d50-96b8-7382af19be82_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable




Hi,
=20
   I am having a requirement here.
=20
   1) I need to write a plpgsql function where it takes the input parameter=
 of a structure of a table.
   2) The table has 15 columns
   3) It does lots of validation based on the parameter and finally returns=
 an integer as output parameters
=20
  Q) How will I passe the table structure as as parameter
      2) Do I need to create a type?
=20
   Please help me.
=20
Regards
skarthi
=20
=20
_________________________________________________________________
Take a break and play crossword puzzles - FREE!
http://games.msn.com/en/flexicon/default.htm?icid=3Dflexicon_ wlmemailtagli=
nemarch07=

--_bd515516-9c52-4d50-96b8-7382af19be82_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
P
{
margin:0px;
padding:0px
}
body
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body>
<BR>
<BLOCKQUOTE>
Hi,<BR>
&nbsp;<BR>
&nbsp;&nbsp; I am having a requirement here.<BR>
&nbsp;<BR>
&nbsp;&nbsp; 1) I need to write a plpgsql function where it takes the input=
 parameter of a structure of a table.<BR>
&nbsp;&nbsp; 2) The table has 15 columns<BR>
&nbsp;&nbsp; 3) It does lots of validation based on the parameter and final=
ly returns an integer as output parameters<BR>
&nbsp;<BR>
&nbsp;&nbsp;Q) How will I passe the table structure as as parameter<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2) Do I need to create a type?<BR>
&nbsp;<BR>
&nbsp;&nbsp; Please help me.<BR>
&nbsp;<BR>
Regards<BR>
skarthi<BR>
&nbsp;<BR>
&nbsp;<BR></BLOCKQUOTE><br /><hr />Take a break and play crossword puzzles =
- FREE! <a href=3D'http://games.msn.com/en/flexicon/default.htm?icid=3Dflex=
icon_ wlmemailtaglinemarch07' target=3D'_new'>Play Now!</a></body>
</html>=

--_bd515516-9c52-4d50-96b8-7382af19be82_--



From akretschmer@spamfence.net Tue Apr  3 18:18:38 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id CE2C79FB864
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 15:18:38 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 58009-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 15:18:31 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from 137086.vserver.de (vs137086.vserver.de [62.75.137.86])
	by postgresql.org (Postfix) with ESMTP id DF4E49FB255
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 15:18:32 -0300 (ADT)
Received: from a-kretschmer.de (p54b39b92.dip0.t-ipconnect.de
 [84.179.155.146])
	(authenticated bits=0)
	by 137086.vserver.de (8.12.8/8.12.8) with ESMTP id l33IIRTL007739
	for <pgsql-sql@postgresql.org>; Tue, 3 Apr 2007 20:18:27 +0200
Received: from kretschmer by a-kretschmer.de with local (Exim 4.61)
	(envelope-from <andreas@a-kretschmer.de>) id 1HYnad-0000vY-Cf
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2007 20:18:43 +0200
Date: Tue, 3 Apr 2007 20:18:43 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: plpgsql function question
Message-ID: <20070403181843.GB26926@KanotixBox>
References: <BAY131-W7F67EF97B4EA5FFB34746B0670@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <BAY131-W7F67EF97B4EA5FFB34746B0670@phx.gbl>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: mutt-ng 1.5.9i (Linux)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/39
X-Sequence-Number: 28039

Karthikeyan Sundaram <skarthi98@hotmail.com> schrieb:

> 
>      Hi,
>       
>         I am having a requirement here.
>       
>         1) I need to write a plpgsql function where it takes the input
>      parameter of a structure of a table.

Because? To build this table? You can pass an ascii-text with the
table-definition and EXECUTE this string.


>         2) The table has 15 columns

Okay. And the problem is?


>         3) It does lots of validation based on the parameter and finally
>      returns an integer as output parameters

Okay. create function ... returns int as $$ ... return 1; end; $$
language plpgsql;


>       
>        Q) How will I passe the table structure as as parameter

As i said, for instance as simple text and EXECUTE this.


>            2) Do I need to create a type?

No.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°



From skarthi98@hotmail.com Tue Apr  3 18:33:56 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id DCFF49FBAB8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 15:33:56 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 73427-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 15:33:40 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from bay0-omc2-s32.bay0.hotmail.com (bay0-omc2-s32.bay0.hotmail.com
	[65.54.246.168])
	by postgresql.org (Postfix) with ESMTP id E2BA79FBA1B
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 15:33:40 -0300 (ADT)
Received: from BAY131-W18 ([65.55.136.53]) by bay0-omc2-s32.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.2668);
	Tue, 3 Apr 2007 11:33:39 -0700
Message-ID: <BAY131-W18D3A18906E05E2B1E90CCB0670@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_47a274a9-43f0-4957-8d64-219b1a59f2df_"
X-Originating-IP: [67.116.31.14]
From: Karthikeyan Sundaram <skarthi98@hotmail.com>
To: Andreas Kretschmer <akretschmer@spamfence.net>, <pgsql-sql@postgresql.org>
Subject: Re: plpgsql function question
Date: Tue, 3 Apr 2007 11:33:39 -0700
Importance: Normal
MIME-Version: 1.0
X-OriginalArrivalTime: 03 Apr 2007 18:33:39.0023 (UTC)
	FILETIME=[98EEC5F0:01C7761E]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/40
X-Sequence-Number: 28040

--_47a274a9-43f0-4957-8d64-219b1a59f2df_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi,
=20
  I guess there is some misunderstanding from my question
=20
  Let me elaborate more clearly.
=20
   My Table is=20
=20
 Create table a (
 i int,
 j varchar(20),
 k date);
=20
Create or replace function a_func (in p_i int, in p_j varchar, in p_k date)=
 returns int as
$$
  ----- do the validation
  return 1;
$$
language 'plpgsql';
=20
This works fine
=20
What I want is something like this
create or replace functinon a_func (in a%rowtype) returns int as
$$
   ---- do the validation
$$
language 'plpgsql';
=20
execute a_func(1, 'good','04/02/2007');
=20
 > Date: Tue, 3 Apr 2007 20:18:43 +0200> From: akretschmer@spamfence.net> T=
o: pgsql-sql@postgresql.org> Subject: Re: [SQL] plpgsql function question> =
> Karthikeyan Sundaram <skarthi98@hotmail.com> schrieb:> > > > > Hi,> > > >=
 I am having a requirement here.> > > > 1) I need to write a plpgsql functi=
on where it takes the input> > parameter of a structure of a table.> > Beca=
use? To build this table? You can pass an ascii-text with the> table-defini=
tion and EXECUTE this string.> > > > 2) The table has 15 columns> > Okay. A=
nd the problem is?> > > > 3) It does lots of validation based on the parame=
ter and finally> > returns an integer as output parameters> > Okay. create =
function ... returns int as $$ ... return 1; end; $$> language plpgsql;> > =
> > > > Q) How will I passe the table structure as as parameter> > As i sai=
d, for instance as simple text and EXECUTE this.> > > > 2) Do I need to cre=
ate a type?> > No.> > > Andreas> -- > Really, I'm not out to destroy Micros=
oft. That will just be a completely> unintentional side effect. (Linus Torv=
alds)> "If I was god, I would recompile penguin with --enable-fly." (unknow=
)> Kaufbach, Saxony, Germany, Europe. N 51.05082=B0, E 13.56889=B0> > -----=
----------------------(end of broadcast)---------------------------> TIP 9:=
 In versions below 8.0, the planner will ignore your desire to> choose an i=
ndex scan if your joining column's datatypes do not> match
_________________________________________________________________
i'm making a difference.=A0Make every IM count for the cause of your choice=
. Join Now.
http://clk.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=3Dhttp://=
im.live.com/messenger/im/home/?source=3Dwlmailtagline=

--_47a274a9-43f0-4957-8d64-219b1a59f2df_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
P
{
margin:0px;
padding:0px
}
body
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body>
Hi,<BR>
&nbsp;<BR>
&nbsp; I guess there is some misunderstanding from my question<BR>
&nbsp;<BR>
&nbsp; Let me elaborate more clearly.<BR>
&nbsp;<BR>
&nbsp;&nbsp; My Table is <BR>
&nbsp;<BR>
&nbsp;Create table a (<BR>
&nbsp;i int,<BR>
&nbsp;j varchar(20),<BR>
&nbsp;k date);<BR>
&nbsp;<BR>
Create or replace function a_func (in p_i int, in p_j varchar, in p_k date)=
 returns int as<BR>
$$<BR>
&nbsp; ----- do the validation<BR>
&nbsp; return 1;<BR>
$$<BR>
language 'plpgsql';<BR>
&nbsp;<BR>
This works fine<BR>
&nbsp;<BR>
What I want is something like this<BR>
create or replace functinon a_func (in a%rowtype) returns int as<BR>
$$<BR>
&nbsp;&nbsp; ---- do the validation<BR>
$$<BR>
language 'plpgsql';<BR>
&nbsp;<BR>
execute a_func(1, 'good','04/02/2007');<BR>
&nbsp;<BR>
&nbsp;<BR><BR><BR>&gt; Date: Tue, 3 Apr 2007 20:18:43 +0200<BR>&gt; From: a=
kretschmer@spamfence.net<BR>&gt; To: pgsql-sql@postgresql.org<BR>&gt; Subje=
ct: Re: [SQL] plpgsql function question<BR>&gt; <BR>&gt; Karthikeyan Sundar=
am &lt;skarthi98@hotmail.com&gt; schrieb:<BR>&gt; <BR>&gt; &gt; <BR>&gt; &g=
t; Hi,<BR>&gt; &gt; <BR>&gt; &gt; I am having a requirement here.<BR>&gt; &=
gt; <BR>&gt; &gt; 1) I need to write a plpgsql function where it takes the =
input<BR>&gt; &gt; parameter of a structure of a table.<BR>&gt; <BR>&gt; Be=
cause? To build this table? You can pass an ascii-text with the<BR>&gt; tab=
le-definition and EXECUTE this string.<BR>&gt; <BR>&gt; <BR>&gt; &gt; 2) Th=
e table has 15 columns<BR>&gt; <BR>&gt; Okay. And the problem is?<BR>&gt; <=
BR>&gt; <BR>&gt; &gt; 3) It does lots of validation based on the parameter =
and finally<BR>&gt; &gt; returns an integer as output parameters<BR>&gt; <B=
R>&gt; Okay. create function ... returns int as $$ ... return 1; end; $$<BR=
>&gt; language plpgsql;<BR>&gt; <BR>&gt; <BR>&gt; &gt; <BR>&gt; &gt; Q) How=
 will I passe the table structure as as parameter<BR>&gt; <BR>&gt; As i sai=
d, for instance as simple text and EXECUTE this.<BR>&gt; <BR>&gt; <BR>&gt; =
&gt; 2) Do I need to create a type?<BR>&gt; <BR>&gt; No.<BR>&gt; <BR>&gt; <=
BR>&gt; Andreas<BR>&gt; -- <BR>&gt; Really, I'm not out to destroy Microsof=
t. That will just be a completely<BR>&gt; unintentional side effect. (Linus=
 Torvalds)<BR>&gt; "If I was god, I would recompile penguin with --enable-f=
ly." (unknow)<BR>&gt; Kaufbach, Saxony, Germany, Europe. N 51.05082=B0, E 1=
3.56889=B0<BR>&gt; <BR>&gt; ---------------------------(end of broadcast)--=
-------------------------<BR>&gt; TIP 9: In versions below 8.0, the planner=
 will ignore your desire to<BR>&gt; choose an index scan if your joining co=
lumn's datatypes do not<BR>&gt; match<BR><br /><hr />i'm making a differenc=
e.=A0Make every IM count for the cause of your choice. <a href=3D'http://cl=
k.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=3Dhttp://im.live.c=
om/messenger/im/home/?source=3Dwlmailtagline' target=3D'_new'>Join Now.</a>=
</body>
</html>=

--_47a274a9-43f0-4957-8d64-219b1a59f2df_--



From andreas.kretschmer@schollglas.com Tue Apr  3 19:24:29 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id A9DA59FB9C1
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 16:24:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 73338-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 16:24:23 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.177])
	by postgresql.org (Postfix) with ESMTP id 07DB39FB9B2
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 16:24:22 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu1) with ESMTP (Nemesis),
	id 0MKwpI-1HYoc82W7v-0004J9; Tue, 03 Apr 2007 21:24:21 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HYoc7-000500-00
	for pgsql-sql@postgresql.org; Tue, 03 Apr 2007 21:24:19 +0200
Date: Tue, 3 Apr 2007 21:24:19 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: plpgsql function question
Message-ID: <20070403192419.GA19007@a-kretschmer.de>
References: <BAY131-W18D3A18906E05E2B1E90CCB0670@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BAY131-W18D3A18906E05E2B1E90CCB0670@phx.gbl>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX19jgWjW+/xOcjUHzO+A6gKSD9FsqHkfHHFKOya
	Xg5LXIz8S0PBtdCGlMqu4hbO3ObBzDdewhGl6K38qTFZhE5hN/
	sMbjVxLca5HSnFIsjvzvKfes+CHFiLI
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/42
X-Sequence-Number: 28042

am  Tue, dem 03.04.2007, um 11:33:39 -0700 mailte Karthikeyan Sundaram folgendes:
> Hi,
>  
>   I guess there is some misunderstanding from my question

Maybe.

>  
>   Let me elaborate more clearly.
>  
>    My Table is
>  
>  Create table a (
>  i int,
>  j varchar(20),
>  k date);
>  
> Create or replace function a_func (in p_i int, in p_j varchar, in p_k date)
> returns int as
> $$
>   ----- do the validation
>   return 1;
> $$
> language 'plpgsql';
>  
> This works fine
>  
> What I want is something like this
> create or replace functinon a_func (in a%rowtype) returns int as
> $$
>    ---- do the validation
> $$
> language 'plpgsql';

What's the reason? For an INSERT or UPDATE - check? You can use a
TRIGGER and check the NEW-structure. Or create a new type, based on the
table-structure. Than you can create your function.

test=# create type a as ( i int, j varchar(20), k date);
CREATE TYPE
test=*# create function my_a (IN foo a) returns int as $$begin return 1; end; $$ language plpgsql;
CREATE FUNCTION
test=*# 


> > Date: Tue, 3 Apr 2007 20:18:43 +0200
> > From: akretschmer@spamfence.net
> > To: pgsql-sql@postgresql.org
> > Subject: Re: [SQL] plpgsql function question

Please, no silly text above and fullquote below, i'm reading from top to
bottom...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net



From desoi@pgedit.com Tue Apr  3 20:27:30 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id B21129FBAB9
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 17:27:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 99975-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  3 Apr 2007 17:27:22 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from a2s5.a2hosting.com (a2s5.a2hosting.com [209.124.50.10])
	by postgresql.org (Postfix) with ESMTP id 138139FBA97
	for <pgsql-sql@postgresql.org>; Tue,  3 Apr 2007 17:27:25 -0300 (ADT)
Received: from adsl-153-116-39.tys.bellsouth.net ([70.153.116.39]
	helo=[192.168.1.12])
	by a2s5.a2hosting.com with esmtpsa (TLSv1:AES128-SHA:128) (Exim 4.63)
	(envelope-from <desoi@pgedit.com>)
	id 1HYpb7-0008P5-67; Tue, 03 Apr 2007 16:27:21 -0400
In-Reply-To: <BAY131-W18D3A18906E05E2B1E90CCB0670@phx.gbl>
References: <BAY131-W18D3A18906E05E2B1E90CCB0670@phx.gbl>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <F2B39579-43CE-4074-8AD1-74760FCA5514@pgedit.com>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>, <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: John DeSoi <desoi@pgedit.com>
Subject: Re: plpgsql function question
Date: Tue, 3 Apr 2007 16:27:16 -0400
To: Karthikeyan Sundaram <skarthi98@hotmail.com>
X-Mailer: Apple Mail (2.752.3)
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - a2s5.a2hosting.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - pgedit.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/44
X-Sequence-Number: 28044

It should work pretty much like you have it. You don't need a type;  
the table is already a type.

Something like:

create or replace function a_func (in p_row a) returns int as
$$
   if p_row.i ...
   if p_row.j ...
$$

If it does not work, show the error and I'll try to dig up an example.

John



On Apr 3, 2007, at 2:33 PM, Karthikeyan Sundaram wrote:

> What I want is something like this
> create or replace functinon a_func (in a%rowtype) returns int as
> $$
>    ---- do the validation
> $$
> language 'plpgsql';
>
> execute a_func(1, 'good','04/02/2007');



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




From andreas.kretschmer@schollglas.com Wed Apr  4 05:09:51 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 0F5DA9FBA63
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  4 Apr 2007 02:09:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 21666-06-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  4 Apr 2007 02:09:32 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.183])
	by postgresql.org (Postfix) with ESMTP id BFE039FBA89
	for <pgsql-sql@postgresql.org>; Wed,  4 Apr 2007 02:01:18 -0300 (ADT)
Received: from [194.25.154.138] (helo=webserv.wug.de)
	by mrelayeu.kundenserver.de (node=mrelayeu8) with ESMTP (Nemesis),
	id 0ML31I-1HYxcT0z6A-0005xM; Wed, 04 Apr 2007 07:01:17 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1HYxcS-0007BJ-00
	for pgsql-sql@postgresql.org; Wed, 04 Apr 2007 07:01:16 +0200
Date: Wed, 4 Apr 2007 07:01:16 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: plpgsql function question
Message-ID: <20070404050116.GA27391@a-kretschmer.de>
References: <BAY131-W2E4CDE0440527D94EA604B0670@phx.gbl>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BAY131-W2E4CDE0440527D94EA604B0670@phx.gbl>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V01U2FsdGVkX1+qi3x3Bl+faUZulEO3EPqNnZclz4gQBebG1uy
	4GXzCzpmPi9hZrD2nzOcozZUPVfXZXorczcZffbSm1OALbnxKB
	MhlMSSZJXtMq61Gk1p8SNsaVDvgnvO5
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/48
X-Sequence-Number: 28048

am  Tue, dem 03.04.2007, um 13:19:26 -0700 mailte Karthikeyan Sundaram folgendes:
> Thank you very much. It works.  I am not doing any insert or update hence I
> cannot create a trigger.  But my another question is
>  
> How will I pass the values to Foo parameters.
>  
> I mean
>  
>  I want to pass
>  i = 1
>  j = 'God'
>  K = now()
>  
> which all the three will be used to check in the function
> should I say
>  
> select a_func(1,'good',now());

Because your function expects one parameter of your new type, you have
to CAST your data into this type:



test=# select * from my_a((1, 'foo', current_date)::a);
 my_a
------
    1
(1 row)


[ dumb fullquote deleted ]


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net



From desoi@pgedit.com Wed Apr  4 12:19:35 2007
Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id B1FFD9FB864
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  4 Apr 2007 09:19:35 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 35711-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Wed,  4 Apr 2007 09:19:20 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from a2s5.a2hosting.com (a2s5.a2hosting.com [209.124.50.10])
	by postgresql.org (Postfix) with ESMTP id 85BE09FB7C3
	for <pgsql-sql@postgresql.org>; Wed,  4 Apr 2007 09:19:25 -0300 (ADT)
Received: from adsl-6-26-39.tys.bellsouth.net ([65.6.26.39]
	helo=[192.168.1.12])
	by a2s5.a2hosting.com with esmtpsa (TLSv1:AES128-SHA:128) (Exim 4.63)
	(envelope-from <desoi@pgedit.com>)
	id 1HZ4SR-00085X-1i; Wed, 04 Apr 2007 08:19:23 -0400
In-Reply-To: <20070404050116.GA27391@a-kretschmer.de>
References: <BAY131-W2E4CDE0440527D94EA604B0670@phx.gbl>
	<20070404050116.GA27391@a-kretschmer.de>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <F345B309-CEDD-43E1-8D2A-A978AE22FE31@pgedit.com>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: John DeSoi <desoi@pgedit.com>
Subject: Re: plpgsql function question
Date: Wed, 4 Apr 2007 08:19:15 -0400
To: A. Kretschmer <andreas.kretschmer@schollglas.com>
X-Mailer: Apple Mail (2.752.3)
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - a2s5.a2hosting.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - pgedit.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/52
X-Sequence-Number: 28052

If you use a plpgsql function to select the row you want to validate,  
it will make life much easier. Something like

...
$$
declare
   my_row a_row_type;
   is_ok integer;
begin
   select into my_row * from a_row_type where ....
   is_ok := my_a_validate(my_row);
   return is_ok;
$$
...

On Apr 4, 2007, at 1:01 AM, A. Kretschmer wrote:

> Because your function expects one parameter of your new type, you have
> to CAST your data into this type:
>
>
>
> test=# select * from my_a((1, 'foo', current_date)::a);
>  my_a
> ------
>     1
> (1 row)



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