Обсуждение: Parse error help needed...
I am getting this error
postgres=# \i a.sql
psql:a.sql:10: ERROR: parser: parse error at or near "return" at character
26
--
a.sql
--
create function loadme() return text as '
Declare
s_out text ;
Begin
For i in 1..10000 loop
insert into test values (i,''Test'');
end loop;
return s_out;
End;
' language 'plpgsql';
Any help is appreciated
Thx
Deep
PS: I already loaded pl/pgsql in my database
[postgres@node1]~$ createlang -d test -l
Procedural languages
Name | Trusted?
---------+----------
plpgsql | t
(1 row)
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Chris Ochs
Sent: Monday, January 12, 2004 4:30 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] sql insert function
Never mind, I forgot to quote the quote's...
Chris
> The documentation doesn't have any examples of using an sql language
> function to do an insert, andI am at loss as to I am doing wrong here.
> The error I get trying to create the function is: ERROR: syntax
> error at or near "$1" at character 148
>
> CREATE FUNCTION taxship(varchar,integer,varchar,float,float) returns
integer
> AS '
> insert into taxship(s_oid,order_id,mer_id,tax,shipping) values
> ('$1',$2,'$3',$4,$5); SELECT 1;
> ' LANGUAGE SQL;
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
"Thapliyal, Deepak" <dthapliyal@soe.sony.com> writes:
> I am getting this error
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR: parser: parse error at or near "return" at character
> 26
> create function loadme() return text as '
^^^^^^
I think you want "returns" here.
-Doug
Thx to doug ... Old error is gone .. But I am now getting new error
postgres=# \i a.sql
psql:a.sql:10: ERROR: language "plpgsql" does not exist
But I verified that I setup plpgsql
[postgres@node1]~$ createlang -d test -l
Procedural languages
Name | Trusted?
---------+----------
plpgsql | t
(1 row)
Can u pls advice if u know whats going on.. Am I missing somin obvious here.
..
Thx in advance
Deep
-----Original Message-----
From: Doug McNaught,,, [mailto:doug@wireboard.com] On Behalf Of Doug
McNaught
Sent: Monday, January 12, 2004 4:44 PM
To: Thapliyal, Deepak
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Parse error help needed...
"Thapliyal, Deepak" <dthapliyal@soe.sony.com> writes:
> I am getting this error
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR: parser: parse error at or near "return" at
> character 26
> create function loadme() return text as '
^^^^^^
I think you want "returns" here.
-Doug
Thapliyal, Deepak wrote: > create function loadme() return text as ' try "RETURNS" instead of "RETURN" [the guys writing the function parser might want to consider reporting what the parser was expecting at this point] > Declare > s_out text ; > Begin > For i in 1..10000 loop > insert into test values (i,''Test''); > end loop; > return s_out; > End; > ' language 'plpgsql'; The rest looks fine (works for me - yes I tested it this time) Alex Satrapa
Made the change and used returns in both places now .. Gives me error
postgres=# \i a.sql
psql:a.sql:10: ERROR: language "plpgsql" does not exist
Any help is appreciated
Thx
Deep
create function loadme() returns text as '
Declare
s_out text ;
Begin
For i in 1..10000 loop
insert into test values (i,''Test'');
end loop;
returns s_out;
End;
' language 'plpgsql';
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Satrapa
Sent: Monday, January 12, 2004 4:57 PM
To: PostgreSQL General
Subject: Re: [GENERAL] Parse error help needed...
Thapliyal, Deepak wrote:
> create function loadme() return text as '
try "RETURNS" instead of "RETURN"
[the guys writing the function parser might want to consider reporting
what the parser was expecting at this point]
> Declare
> s_out text ;
> Begin
> For i in 1..10000 loop
> insert into test values (i,''Test'');
> end loop;
> return s_out;
> End;
> ' language 'plpgsql';
The rest looks fine (works for me - yes I tested it this time)
Alex Satrapa
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--On Monday, January 12, 2004 17:24:14 -0800 "Thapliyal, Deepak" <dthapliyal@soe.sony.com> wrote: > Made the change and used returns in both places now .. Gives me error > > postgres=# \i a.sql > psql:a.sql:10: ERROR: language "plpgsql" does not exist > createlang plpgsql > Any help is appreciated > > Thx > Deep > > create function loadme() returns text as ' > Declare > s_out text ; > Begin > For i in 1..10000 loop > insert into test values (i,''Test''); > end loop; > returns s_out; > End; > ' language 'plpgsql'; > > > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Satrapa > Sent: Monday, January 12, 2004 4:57 PM > To: PostgreSQL General > Subject: Re: [GENERAL] Parse error help needed... > > > Thapliyal, Deepak wrote: >> create function loadme() return text as ' > > try "RETURNS" instead of "RETURN" > > [the guys writing the function parser might want to consider reporting > what the parser was expecting at this point] > >> Declare >> s_out text ; >> Begin >> For i in 1..10000 loop >> insert into test values (i,''Test''); >> end loop; >> return s_out; >> End; >> ' language 'plpgsql'; > > The rest looks fine (works for me - yes I tested it this time) > > Alex Satrapa > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Вложения
Now my function is created ... Thanks guys ..
Now getting error while trying to execute it
postgres=# select loadme();
WARNING: Error occurred while executing PL/pgSQL function loadme
WARNING: line 7 at SQL statement
ERROR: SPI_prepare() failed on "commit"
Thx again
Deep
PS: here is fn definition
create function loadme() returns text as '
Declare
s_out text ;
Begin
For i in 1..10000 loop
insert into test values (i,''Test'');
end loop;
commit;
returns s_out;
End;
' language 'plpgsql';
-----Original Message-----
From: Larry Rosenman [mailto:ler@lerctr.org]
Sent: Monday, January 12, 2004 5:29 PM
To: Thapliyal, Deepak; 'PostgreSQL General'
Subject: Re: [GENERAL] Parse error help needed...
--On Monday, January 12, 2004 17:24:14 -0800 "Thapliyal, Deepak"
<dthapliyal@soe.sony.com> wrote:
> Made the change and used returns in both places now .. Gives me error
>
> postgres=# \i a.sql
> psql:a.sql:10: ERROR: language "plpgsql" does not exist
>
createlang plpgsql
> Any help is appreciated
>
> Thx
> Deep
>
> create function loadme() returns text as '
> Declare
> s_out text ;
> Begin
> For i in 1..10000 loop
> insert into test values (i,''Test'');
> end loop;
> returns s_out;
> End;
> ' language 'plpgsql';
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Satrapa
> Sent: Monday, January 12, 2004 4:57 PM
> To: PostgreSQL General
> Subject: Re: [GENERAL] Parse error help needed...
>
>
> Thapliyal, Deepak wrote:
>> create function loadme() return text as '
>
> try "RETURNS" instead of "RETURN"
>
> [the guys writing the function parser might want to consider reporting
> what the parser was expecting at this point]
>
>> Declare
>> s_out text ;
>> Begin
>> For i in 1..10000 loop
>> insert into test values (i,''Test'');
>> end loop;
>> return s_out;
>> End;
>> ' language 'plpgsql';
>
> The rest looks fine (works for me - yes I tested it this time)
>
> Alex Satrapa
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
On Tuesday 13 January 2004 01:39, Thapliyal, Deepak wrote: > Now my function is created ... Thanks guys .. > > Now getting error while trying to execute it > > postgres=# select loadme(); > WARNING: Error occurred while executing PL/pgSQL function loadme > WARNING: line 7 at SQL statement > ERROR: SPI_prepare() failed on "commit" Remove the "commit" line - functions cannot define their own transactions anyway. I guess you're being misled by the "begin" line - it's matched by the "end" and defines the function body rather than any transaction. -- Richard Huxton Archonet Ltd
RH> Remove the "commit" line - functions cannot define their own transactions RH> anyway. Do you know if it will be solved sometime? Or this is architecture dependend problem? I mean that transactions are rulez and very helpful rulez when working with large databases. Regards, Anton
On Tuesday 13 January 2004 12:01, Anton.Nikiforov@loteco.ru wrote: > RH> Remove the "commit" line - functions cannot define their own > transactions RH> anyway. > Do you know if it will be solved sometime? Or this is architecture > dependend problem? I mean that transactions are rulez and very helpful > rulez when working with large databases. Nested transactions are on the todo list, but I don't know when they will appear. -- Richard Huxton Archonet Ltd