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 -------Original Message------- Date: 09/01/05 15:57:21 Subject: Re: [GENERAL] Problem running or executing a function in Postgresql 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 message can get through to the mailing list cleanly |