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

-------Original Message-------

From: Richard Huxton
Date: 09/01/05 15:57:21
To: Venki
Cc: pgsql-general(at)postgresql(dot)org
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
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly