Thread: Writing a user defined function
Hello, I am trying to code a simple udf in postgres. How do I write sql commands into pl/sql ? The foll. code doesnt work. CREATE OR REPLACE FUNCTION udf() RETURNS integer AS $$ BEGIN for i in 1..2000 loop for j in 1...10000 loop end loop; begin work; declare cust scroll cursor for select * from tpcd.customer; FETCH FORWARD 5 FROM cust; end loop; CLOSE cust; COMMIT work; return 1; end; $$ LANGUAGE plpgsql; select udf(); thanks, Suresh -- View this message in context: http://www.nabble.com/Writing-a-user-defined-function-tp18532591p18532591.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, Jul 18, 2008 at 12:07 PM, Suresh_ <suiyengar@yahoo.com> wrote: > > Hello, > I am trying to code a simple udf in postgres. How do I write sql commands > into pl/sql ? The foll. code doesnt work. > > CREATE OR REPLACE FUNCTION udf() > RETURNS integer AS $$ > BEGIN > for i in 1..2000 loop > for j in 1...10000 loop > end loop; > begin work; Postgres doesn't let you do transactions inside a function. Take out the BEGIN and COMMIT, and if you still get errors post the function code and the error message that you get. -Doug
On 18/07/2008 17:07, Suresh_ wrote:
> CREATE OR REPLACE FUNCTION udf()
> RETURNS integer AS $$
> BEGIN
> for i in 1..2000 loop
> for j in 1...10000 loop
> end loop;
> begin work;
^^^^^^----- Here's your problem!
You can't have a transaction inside a function - the function is already
executed inside a transaction.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
I get this error ERROR: syntax error at or near "cursor" CONTEXT: invalid type name "scroll cursor for select * from tpcd.customer" compile of PL/pgSQL function "udf" near line 5 Douglas McNaught wrote: > > On Fri, Jul 18, 2008 at 12:07 PM, Suresh_ <suiyengar@yahoo.com> wrote: >> >> Hello, >> I am trying to code a simple udf in postgres. How do I write sql >> commands >> into pl/sql ? The foll. code doesnt work. >> >> CREATE OR REPLACE FUNCTION udf() >> RETURNS integer AS $$ >> BEGIN >> for i in 1..2000 loop >> for j in 1...10000 loop >> end loop; >> begin work; > > Postgres doesn't let you do transactions inside a function. > > Take out the BEGIN and COMMIT, and if you still get errors post the > function code and the error message that you get. > > -Doug > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Writing-a-user-defined-function-tp18532591p18551845.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hello what is version of your postgresql? regards Pavel Stehule 2008/7/20 Suresh_ <suiyengar@yahoo.com>: > > I get this error > > ERROR: syntax error at or near "cursor" > CONTEXT: invalid type name "scroll cursor for select * from tpcd.customer" > compile of PL/pgSQL function "udf" near line 5 > > > Douglas McNaught wrote: >> >> On Fri, Jul 18, 2008 at 12:07 PM, Suresh_ <suiyengar@yahoo.com> wrote: >>> >>> Hello, >>> I am trying to code a simple udf in postgres. How do I write sql >>> commands >>> into pl/sql ? The foll. code doesnt work. >>> >>> CREATE OR REPLACE FUNCTION udf() >>> RETURNS integer AS $$ >>> BEGIN >>> for i in 1..2000 loop >>> for j in 1...10000 loop >>> end loop; >>> begin work; >> >> Postgres doesn't let you do transactions inside a function. >> >> Take out the BEGIN and COMMIT, and if you still get errors post the >> function code and the error message that you get. >> >> -Doug >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >> > > -- > View this message in context: http://www.nabble.com/Writing-a-user-defined-function-tp18532591p18551845.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
| Hello, Version is 8.1.3. Its an older version, in which I have some custom code. I want to test the code with a function which has a seq scan and a blocking loop. Thanks, Suresh --- On Sun, 7/20/08, Pavel Stehule <pavel.stehule@gmail.com> wrote: From: Pavel Stehule <pavel.stehule@gmail.com> |
Hello 2008/7/20 Suresh <suiyengar@yahoo.com>: > Hello, > > Version is 8.1.3. Its an older version, in which I have some custom code. > I want to test the code with a function which has a seq scan and a blocking > loop. > first, scrollable cursors are supported from 8.3 second, you cannot declare cursor inside block - see on plpgsql documentation http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html regards Pavel Stehule > Thanks, > Suresh > > --- On Sun, 7/20/08, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > From: Pavel Stehule <pavel.stehule@gmail.com> > Subject: Re: [GENERAL] Writing a user defined function > To: "Suresh_" <suiyengar@yahoo.com> > Cc: pgsql-general@postgresql.org > Date: Sunday, July 20, 2008, 1:33 AM > > Hello > > what is version of your postgresql? > > regards > Pavel Stehule > > 2008/7/20 Suresh_ <suiyengar@yahoo.com>: >> >> I get this error >> >> ERROR: syntax error at or near "cursor" >> > CONTEXT: invalid type name "scroll cursor for select * from > tpcd.customer" >> compile of PL/pgSQL function "udf" near line 5 >> >> >> Douglas McNaught wrote: >>> >>> On Fri, Jul 18, 2008 at 12:07 PM, Suresh_ <suiyengar@yahoo.com> > wrote: >>>> >>>> Hello, >>>> I am trying to code a simple udf in postgres. How do I write sql >>>> commands >>>> into pl/sql ? The foll. code doesnt work. >>>> >>>> CREATE OR REPLACE FUNCTION udf() >>>> RETURNS integer AS $$ >>>> BEGIN >>>> for i in 1..2000 loop >>>> for j in 1...10000 loop >>>> end loop; >>>> begin work; >>> >>> Postgres doesn't let you do transactions inside a function. >>> >>> Take out the BEGIN and COMMIT, and if you still get errors post the >>> function code and the error > message that you get. >>> >>> -Doug >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >>> >> >> -- >> View this message in context: > http://www.nabble.com/Writing-a-user-defined-function-tp18532591p18551845.html >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >
Hello, I want to profile some functions in postgres using gprof. I am following this procedure listed in nabble forum. # cd postgres/bin # cd .../src/backend # make clean # make PROFILE="-pg" all How to proceed after this ? I am not getting the gmon.out file. How do I run ./postgres test under the profiler ? thanks and regards, Suresh |
configure postgres with the following options 1. ./configure --enable-profiling 2. make and make install 3. start psql and issue the query 4. gmon.out will be in the data directory once you exit from psql -Sushant. On Sun, 2008-07-27 at 05:55 -0700, Suresh wrote: > > Hello, > > I want to profile some functions in postgres using gprof. I am > following this procedure listed in nabble forum. > > # cd postgres/bin > # cd .../src/backend > # make clean > # make PROFILE="-pg" all > > How to proceed after this ? I am not getting the gmon.out file. How do > I run ./postgres test under the profiler ? > > thanks and regards, > Suresh > > > >