Thread: Writing a user defined function

Writing a user defined function

From
Suresh_
Date:
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.


Re: Writing a user defined function

From
"Douglas McNaught"
Date:
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

Re: Writing a user defined function

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

Re: Writing a user defined function

From
Suresh_
Date:
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.


Re: Writing a user defined function

From
"Pavel Stehule"
Date:
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
>

Re: Writing a user defined function

From
Suresh
Date:
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>
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 errormessage 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
>

Re: Writing a user defined function

From
"Pavel Stehule"
Date:
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
>>
>

Profiling postgres

From
Suresh
Date:

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



Re: Profiling postgres

From
Sushant Sinha
Date:
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
>
>
>
>