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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
