Thread: plpgsql function question
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.
2) The table has 15 columns
3) It does lots of validation based on the parameter and finally returns an integer as output parameters
Q) How will I passe the table structure as as parameter
2) Do I need to create a type?
Please help me.
Regards
skarthi
Take a break and play crossword puzzles - FREE! Play Now!
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°
Hi,<br /> <br /> I guess there is some misunderstanding from my question<br /> <br /> Let me elaborate more clearly.<br/> <br /> My Table is <br /> <br /> Create table a (<br /> i int,<br /> j varchar(20),<br /> k date);<br/> <br /> Create or replace function a_func (in p_i int, in p_j varchar, in p_k date) returns int as<br /> $$<br/> ----- do the validation<br /> return 1;<br /> $$<br /> language 'plpgsql';<br /> <br /> This works fine<br /> <br /> What I want is something like this<br /> create or replace functinon a_func (in a%rowtype) returns int as<br />$$<br /> ---- do the validation<br /> $$<br /> language 'plpgsql';<br /> <br /> execute a_func(1, 'good','04/02/2007');<br/> <br /> <br /><br /><br />> Date: Tue, 3 Apr 2007 20:18:43 +0200<br />> From: akretschmer@spamfence.net<br/>> To: pgsql-sql@postgresql.org<br />> Subject: Re: [SQL] plpgsql function question<br/>> <br />> Karthikeyan Sundaram <skarthi98@hotmail.com> schrieb:<br />> <br />> > <br />>> Hi,<br />> > <br />> > I am having a requirement here.<br />> > <br />> > 1) I need towrite a plpgsql function where it takes the input<br />> > parameter of a structure of a table.<br />> <br />>Because? To build this table? You can pass an ascii-text with the<br />> table-definition and EXECUTE this string.<br/>> <br />> <br />> > 2) The table has 15 columns<br />> <br />> Okay. And the problem is?<br/>> <br />> <br />> > 3) It does lots of validation based on the parameter and finally<br />> > returnsan integer as output parameters<br />> <br />> Okay. create function ... returns int as $$ ... return 1; end;$$<br />> language plpgsql;<br />> <br />> <br />> > <br />> > Q) How will I passe the table structureas as parameter<br />> <br />> As i said, for instance as simple text and EXECUTE this.<br />> <br />><br />> > 2) Do I need to create a type?<br />> <br />> No.<br />> <br />> <br />> Andreas<br/>> -- <br />> Really, I'm not out to destroy Microsoft. That will just be a completely<br />> unintentionalside effect. (Linus Torvalds)<br />> "If I was god, I would recompile penguin with --enable-fly." (unknow)<br/>> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°<br />> <br />> ---------------------------(endof broadcast)---------------------------<br />> TIP 9: In versions below 8.0, the plannerwill ignore your desire to<br />> choose an index scan if your joining column's datatypes do not<br />> match<br/><br /><hr />i'm making a difference. Make every IM count for the cause of your choice. <a href="http://clk.atdmt.com/MSN/go/msnnkwme0080000001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=wlmailtagline" target="_new">JoinNow.</a>
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
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
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
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