|
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 | 