Re: Problem running or executing a function in Postgresql

Dear Mr. Richard Huxton & Michael Fuhr,

    Thanks for your prompt replies, it has helped me a lot. The problem was solved by using this line

 SELECT INTO new_id currval(''mydata_id_seq'');

 

Thanks again for your valuable suggestions

 

Regards,

venki

 

 

 

Venki wrote:

>  Hi,

>

> I have a table named mydata

> CREATE TABLE public.mydata (

> id int4 DEFAULT nextval('public.mydata_id_seq'::text) NOT NULL,

> name varchar(50)

> ) WITH OIDS;

>

> and I have a function as follows

> CREATE OR REPLACE FUNCTION insertmydata(varchar) RETURNS int

> as '

> declare

> new_id integer;

> begin

> INSERT INTO mydata("name") values($1);

> new_id = EXECUTE("SELECT FROM currval("mydata_id_seq")");

> return new_id;

> end;

> '

> LANGUAGE 'PLPGSQL';

>

> when I run the function as

> select insertmydata('Venkatesh')

>

> I am getting the following error message

> "ERROR:  syntax error at or near "mydata_id_seq" at character 39"

 

OK well, let's look at the line it's suggesting has a problem:

 

  > new_id = EXECUTE("SELECT FROM currval("mydata_id_seq")");

 

Well, there are two main things wrong with this. Firstly, the quoting is

very suspect. You're using double-quotes (") to represent a string

(rather than quoting a named object to preserve its case) and then

you've nested them. Strings need to use escaped single-quotes (either

doubled-up '' or with a backslash \')

 

Secondly, you can't use EXECUTE like that, it doesn't return a value.

There's no dynamic element to the query so it's unnecessary. Perhaps:

   SELECT INTO new_id currval(''mydata_id_seq'');

 

In your particular example, it's just a function-call anyway, so you can

use simple assignment.

   new_id := currval(''mydata_id_seq'');

 

See if that helps.

--

   Richard Huxton

   Archonet Ltd

 

---------------------------(end of broadcast)---------------------------

TIP 1: if posting/reading through Usenet, please send an appropriate

       subscribe-nomail command to majordomo@postgresql.org so that your

       message can get through to the mailing list cleanly