Re: Problem running or executing a function in Postgresql - Mailing list pgsql-general

From Venki
Subject Re: Problem running or executing a function in Postgresql
Date
Msg-id 4316E0E8.000008.00380@VENKI
Whole thread Raw
In response to Re: Problem running or executing a function in Postgresql  (Richard Huxton <dev@archonet.com>)
List pgsql-general
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
To: Venki
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@postgresql.org so that your
       message can get through to the mailing list cleanly

pgsql-general by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: Out Of Memory Error on GiST Index
Next
From: Kelly Burkhart
Date:
Subject: Re: optimum settings for dedicated box