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

On Thu, Sep 01, 2005 at 11:58:27AM +0530, Venki wrote:
>
> new_id = EXECUTE("SELECT FROM currval("mydata_id_seq")");

The above line has quoting problems and an erroneous SELECT query,
and it wouldn't work anyway because that's not the way to retrieve
results from EXECUTE.  Aside from that, EXECUTE isn't necessary in
this case.  Try this:

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

--
Michael Fuhr