Обсуждение: Problem running or executing a function in Postgresql

Поиск
Список
Период
Сортировка

Problem running or executing a function in Postgresql

От
"Venki"
Дата:
 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"

can anyone help me in solving the above problem. Am I missing something here? How should I run the function to insert data?

I am using postgresql version 7.4.6 in a linux box and running pgadmin from a Winxp PC.

 
Kind Regards,
Venki

Re: Problem running or executing a function in Postgresql

От
Richard Huxton
Дата:
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

Re: Problem running or executing a function in Postgresql

От
Michael Fuhr
Дата:
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

Re: Problem running or executing a function in Postgresql

От
"Venki"
Дата:
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