<h3 class="EP8xU" style="color: rgb(121, 6, 25);">kubo $B$5$s(B</h3>              Thank you for your kind reply... I got solution for my problem after your instrunctions.... <div class="gmail_quote">On Tue, Dec 2, 2008 at 6:02 PM, kubo <<a href="mailto:dbflute@gmail.com">dbflute@gmail.com</a>> wrote: <blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">$B5WJ](B(jflute)$B$G$9!#(B Hello, John <div class="Ih2E3d"> > I am new to dbflute. </div>Thank you for your using DBFlute! I answer by both English and Japanese because I am NOT good at English. So if you have someone who understands Japanese, please request him to read the Japanese answer. [English Answer] The creating SQL functions in outside-SQL is unsupported now. ('creating' means DDL 'CREATE OR REPLACE FUNCTION') Which is your purpose about SQL functions in outside-SQL  Creating or Calling? If it's Calling(Maybe I think it's Calling), Please create the SQL function at the timing of creating database schema? 1. prepare DDL contains SQL functions 2. run ReplaceSchema 3. run JDBC and Generate and Sql2Entity 4. call SQL function by behavior's outside-SQL Please check out 'dbflute-postgresql-example' from SVN repogitry. And refer to it. This example project has the example of the SQL functions for PostgreSQL. <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example</a> A. Please watch the SQL(DDL) for replace-schema that contains creating SQL functions. <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/playsql/replace-schema-vendorcheck.sql" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/playsql/replace-schema-vendorcheck.sql</a> B. Please watch the setting for stored procedures of outside-SQL. <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/dfprop/outsideSqlDefinitionMap.dfprop" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/dfprop/outsideSqlDefinitionMap.dfprop</a> C. Please watch the class 'VendorCheckTest.java' that contains calling SQL functions. <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/src/test/java/com/example/dbflute/postgresql/dbflute/various/VendorCheckTest.java" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/src/test/java/com/example/dbflute/postgresql/dbflute/various/VendorCheckTest.java</a> If you have doubt about detail again, please question again. [Japanese Answer] $B8=:_$O30$@$7(BSQL$B$NCf$G%U%!%s%/%7%g%s$r:n@.$9$k$3$H$O%5%]!<%H$5$l$^$;$s!#(B ($B$3$3$G$$$&:n@.$9$k$H$$$&$N$O(B'CREATE OR REPLACE FUNCTION'$B$N(BDDL$BJ8$3$H$G$9(B) $B30$@$7(BSQL$B$NCf$G%U%!%s%/%7%g%s$r07$&L\E*$O!"(B $B!V:n@.$9$k$3$H!W$G$7$g$&$+!)$=$l$H$b!V8F$S=P$9$3$H!W$G$7$g$&$+!)(B $B$b$7!"!V8F$S=P$9$3$H!W$J$N$G$"$l$P(B($BB?J,!V8F$S=P$9$3$H!W$@$H;W$$$^$9$,(B)$B!"(B $B%G!<%?%Y!<%9%9%-!<%^$r:n@.$9$k;~E@$G$=$N%U%!%s%/%7%g%s$r:n@.$7$F2<$5$$!#(B 1. $B%U%!%s%/%7%g%s$r4^$s$@(BDDL$B$r=`Hw(B 2. ReplaceSchema$B$r<B9T(B 3. JDBC$B$H(BGenerate$B$H(BSql2Entity$B$r<B9T(B 4. Behavior$B$N(Boutside-SQL$B$+$i%U%!%s%/%7%g%s$r8F$S=P$9(B SVN$B%j%]%8%H%j$+$i(B'dbflute-postgresql-example'$B$r%A%'%C%/%"%&%H$7$F$=$l$r;29M$K$7$F2<$5$$!#(B $B$3$N(BExample$B%W%m%8%'%/%H$K$O(BPostgreSQL$B$N%U%!%s%/%7%g%s$K4X$9$k(BExample$B$,$"$j$^$9!#(B <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example</a> 1. $B%U%!%s%/%7%g%s$r:n@.$7$F$$$k(BReplaceSchema$BMQ$N(BSQL(DDL)$B$r$4Mw$/$@$5$$!#(B <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/playsql/replace-schema-vendorcheck.sql" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/playsql/replace-schema-vendorcheck.sql</a> 2. $B30$@$7(BSQL$B$N%9%H%"%I%W%m%7!<%8%c$N@_Dj$r$4Mw$/$@$5$$!#(B <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/dfprop/outsideSqlDefinitionMap.dfprop" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/dbflute_exampledb/dfprop/outsideSqlDefinitionMap.dfprop</a> 3. $B%U%!%s%/%7%g%s$r8F$S=P$7$F$$$k(B'VendorCheckTest.java'$B%/%i%9$r$4Mw$/$@$5$$!#(B <a href="https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/src/test/java/com/example/dbflute/postgresql/dbflute/various/VendorCheckTest.java" target="_blank">https://www.seasar.org/svn/sandbox/dbflute/trunk/dbflute-postgresql-example/src/test/java/com/example/dbflute/postgresql/dbflute/various/VendorCheckTest.java</a> $B$b$7!":Y$+$$$H$3$m$G5?Ld$,$"$j$^$7$?$i:FEY$4<ALd$/$@$5$$!#(B 2008/12/2 John Peter <<a href="mailto:jp.mcy.10@gmail.com">jp.mcy.10@gmail.com</a>>: <div><div></div><div class="Wj3C7c">> Hai all, > >           I am new to dbflute. > I have one doubt.can dbflute support SQL functions. > > i wrote one SQL functions,but i have to use this function in dbflute > > can any body help me in this..... > > > Here i attached my sql Function > > > -- #GrowthRateDealerFunction# > -- !GrowthRateDealerFunctionPmb! > -- !!String Nsccode!! > -- !!Date fromDate1!! > -- !!Date toDate1!! > -- !!Date fromDate2!! > -- !!Date toDate2!! > > CREATE OR REPLACE FUNCTION get_GrowthRate_dealer() >   RETURNS SETOF growth_dealer_type >   AS ' > DECLARE >   growth growth_dealer_type; >   dealer RECORD; > BEGIN >   FOR dealer IN >     select distinct tdealer.dealercode from mnsc,tdealer,tdos where > tdealer.nsccode=mnsc.nsccode > and tdealer.dealercode=tdos.dealercode and > /*IF GrowthRateDealerFunctionPmb.nsccode!= null*/mnsc.nsccode=/*IF > GrowthRateDealerFunctionPmb.nsccode*/''China'' > >   LOOP > >     growth.dealercode:=dealer.dealercode; >     growth.periodone:=(select count(*) from tdos,tdealer,mnsc,mstandard > where tdos.standardcode = mstandard.standardcode and > tdos.dealercode=tdealer.dealercode and > tdealer.nsccode = mnsc.nsccode and > tdos.dosresult = 1 and > tdealer.dealercode=dealer.dealercode and > tdos.dosnakbn = 0 and > tdos.dosdatersv1 between > /*GrowthRateDealerFunctionPmb.fromDate1*/''2006/11/1'' and > /*GrowthRateDealerFunctionPmb.toDate1*/ ''2007/4/1''); > growth.periodtwo:=(select count(*) from tdos,tdealer,mnsc,mstandard > where tdos.standardcode = mstandard.standardcode and > tdos.dealercode=tdealer.dealercode and > tdealer.nsccode = mnsc.nsccode and > tdealer.dealercode=dealer.dealercode and > tdos.dosresult = 1 and > tdos.dosnakbn = 0 and > tdos.dosdatersv1 between > /*GrowthRateDealerFunctionPmb.fromDate2*/''2007/4/1'' and > /*GrowthRateDealerFunctionPmb.toDate2*/''2008/11/1''); > if(growth.periodtwo!=0) then > growth.growth_rate = > (round((growth.periodtwo-growth.periodone)*100/(1.0*growth.periodtwo),0)); > else > growth.growth_rate =0; > end if; >     RETURN NEXT growth; >   END LOOP; >   RETURN; > END; > ' LANGUAGE plpgsql STRICT; > > This is my SQL function. > > if i run SQL2Entity,it shows following Error.. > > BUILD FAILED > C:\AgnieWork\workspace\nsdos\mydbflute\dbflute-0.8.4\build-torque.xml:323: > org.s > easar.dbflute.helper.jdbc.sqlfile.DfSQLExecutionFailureException: Look! Read > the >  message below. > /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * > It failed to execute the SQL! > > [SQL File] > ..\src\main\resources\sql\growthrate\growthrateDealer.sql > > [Executed SQL] > -- #GrowthRateDealerFunction# > -- !GrowthRateDealerFunctionPmb! > -- !!String Nsccode!! > -- !!Date fromDate1!! > -- !!Date toDate1!! > -- !!Date fromDate2!! > -- !!Date toDate2!! > > CREATE OR REPLACE FUNCTION get_GrowthRate_dealer() >   RETURNS SETOF growth_dealer_type >   AS ' > DECLARE >   growth growth_dealer_type > > [SQLState] > 42601 > > [ErrorCode] > 0 > > [SQLException] > org.postgresql.util.PSQLException > ERROR: unterminated quoted string at or near "' > DECLARE >   growth growth_dealer_type" > * * * * * * * * * */ > > Thanks in advance > $B%H%^%9(B > </div></div>> _______________________________________________ > Seasar-user mailing list > <a href="mailto:Seasar-user@ml.seasar.org">Seasar-user@ml.seasar.org</a> > <a href="https://ml.seasar.org/mailman/listinfo/seasar-user" target="_blank">https://ml.seasar.org/mailman/listinfo/seasar-user</a> > > _______________________________________________ Seasar-user mailing list <a href="mailto:Seasar-user@ml.seasar.org">Seasar-user@ml.seasar.org</a> <a href="https://ml.seasar.org/mailman/listinfo/seasar-user" target="_blank">https://ml.seasar.org/mailman/listinfo/seasar-user</a> </blockquote></div>