473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

parse error in function

Hello!

I have problem with my function and I can find what's wrong.

WARNING: Error occurred while executing PL/pgSQL function fn_insert_entry_pending
WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near "$1" at character 58

create or replace function "fn_insert_entry_pending"(varchar,varchar,varchar, varchar,varchar,boolean,boolean,date,int,int) returns integer as '
declare
email alias for $1;
caption alias for $2;
description alias for $3;
keywords alias for $4;
url alias for $5;
is_company alias for $6;
is_novelty alias for $7;
expire alias for $8;
category1 alias for $9;
category2 alias for $10;
tmp record;
id_entry_tmp integer;

begin
SELECT INTO tmp id_user FROM "user" WHERE id_user = email;
if not found then
execute ''INSERT INTO "user" (id_user) VALUES ('' || quote_literal(email) || '')'';
end if;
select into id_entry_tmp id_entry from "directory_entry" where url=url and is_novelty=is_novelty;

DELETE FROM "directory_entry_pending" WHERE "url"=url and "id_entry"=id_entry_tmp and "is_novelty"=is_novelty;

INSERT INTO "directory_entry_pending" (id_entry,id_user,caption,url,is_company,is_novelt y,expire,hash) VALUES (id_entry_tmp,$1,$2,$5,$6,$7,$8,''1'');

return 1;
end;
' language 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #1
8 3739
Have you got any funny characters in your function definition (like a TAB
character) - I can't tell from your e-mail.

I've seen problems before inserting function using psql when this is the
case.

John Sidney-Woollett

Uros said:
Hello!

I have problem with my function and I can find what's wrong.

WARNING: Error occurred while executing PL/pgSQL function
fn_insert_entry_pending
WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near "$1" at character 58

create or replace function
"fn_insert_entry_pending"(varchar,varchar,varchar, varchar,varchar,boolean,boolean,date,int,int)
returns integer as '
declare
email alias for $1;
caption alias for $2;
description alias for $3;
keywords alias for $4;
url alias for $5;
is_company alias for $6;
is_novelty alias for $7;
expire alias for $8;
category1 alias for $9;
category2 alias for $10;
tmp record;
id_entry_tmp integer;

begin
SELECT INTO tmp id_user FROM "user" WHERE id_user = email;
if not found then
execute ''INSERT INTO "user" (id_user) VALUES ('' ||
quote_literal(email) || '')'';
end if;
select into id_entry_tmp id_entry from "directory_entry" where url=url
and is_novelty=is_novelty;

DELETE FROM "directory_entry_pending" WHERE "url"=url and
"id_entry"=id_entry_tmp and "is_novelty"=is_novelty;

INSERT INTO "directory_entry_pending"
(id_entry,id_user,caption,url,is_company,is_novelt y,expire,hash)
VALUES (id_entry_tmp,$1,$2,$5,$6,$7,$8,''1'');

return 1;
end;
' language 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #2
Hello John,

This happen when i run my function and I check for this characters. I have
only spaces.

Thursday, January 15, 2004, 3:29:35 PM, you wrote:

JSW> Have you got any funny characters in your function definition (like a TAB
JSW> character) - I can't tell from your e-mail.

JSW> I've seen problems before inserting function using psql when this is the
JSW> case.

JSW> John Sidney-Woollett

JSW> Uros said:
Hello!

I have problem with my function and I can find what's wrong.

WARNING: Error occurred while executing PL/pgSQL function
fn_insert_entry_pending
WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near "$1" at character 58

create or replace function
"fn_insert_entry_pending"(varchar,varchar,varchar, varchar,varchar,boolean,boolean,date,int,int)
returns integer as '
declare
email alias for $1;
caption alias for $2;
description alias for $3;
keywords alias for $4;
url alias for $5;
is_company alias for $6;
is_novelty alias for $7;
expire alias for $8;
category1 alias for $9;
category2 alias for $10;
tmp record;
id_entry_tmp integer;

begin
SELECT INTO tmp id_user FROM "user" WHERE id_user = email;
if not found then
execute ''INSERT INTO "user" (id_user) VALUES ('' ||
quote_literal(email) || '')'';
end if;
select into id_entry_tmp id_entry from "directory_entry" where url=url
and is_novelty=is_novelty;

DELETE FROM "directory_entry_pending" WHERE "url"=url and
"id_entry"=id_entry_tmp and "is_novelty"=is_novelty;

INSERT INTO "directory_entry_pending"
(id_entry,id_user,caption,url,is_company,is_novelt y,expire,hash)
VALUES (id_entry_tmp,$1,$2,$5,$6,$7,$8,''1'');

return 1;
end;
' language 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #3
Apologies, I jumped the gun, amd misread your e-mail.

Isn't the syntax for "select into"...

select column1, column2 into variable1, variable2 from {yourtable} where
{some-condition};

Your statement would be:

SELECT id_user INTO tmp FROM user WHERE id_user = email;

See http://www.postgres.org/docs/current...electinto.html

Hope that helps.

John

Uros said:
Hello John,

This happen when i run my function and I check for this characters. I have only spaces.

Thursday, January 15, 2004, 3:29:35 PM, you wrote:

JSW> Have you got any funny characters in your function definition (like a TAB
JSW> character) - I can't tell from your e-mail.

JSW> I've seen problems before inserting function using psql when this is the
JSW> case.

JSW> John Sidney-Woollett

JSW> Uros said:
Hello!
I have problem with my function and I can find what's wrong.
WARNING: Error occurred while executing PL/pgSQL function
fn_insert_entry_pending
WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near "$1" at character 58
create or replace function
"fn_insert_entry_pending"(varchar,varchar,varchar, varchar,varchar,boolean,boolean,date,int,int) returns integer as ' declare
email alias for $1;
caption alias for $2;
description alias for $3;
keywords alias for $4;
url alias for $5;
is_company alias for $6;
is_novelty alias for $7;
expire alias for $8;
category1 alias for $9;
category2 alias for $10;
tmp record;
id_entry_tmp integer;
begin
SELECT INTO tmp id_user FROM "user" WHERE id_user = email; if not found then execute ''INSERT INTO "user" (id_user) VALUES ('' ||
quote_literal(email) || '')'';
end if;
select into id_entry_tmp id_entry from "directory_entry" where
url=url
and is_novelty=is_novelty;
DELETE FROM "directory_entry_pending" WHERE "url"=url and
"id_entry"=id_entry_tmp and "is_novelty"=is_novelty;
INSERT INTO "directory_entry_pending"
(id_entry,id_user,caption,url,is_company,is_novelt y,expire,hash) VALUES (id_entry_tmp,$1,$2,$5,$6,$7,$8,''1''); return 1;
end;
' language 'plpgsql';
---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


---------------------------(end of broadcast)---------------------------

TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to ma*******@postgresql.org)



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #4
On Thursday 15 January 2004 14:25, Uros wrote:
Hello!

I have problem with my function and I can find what's wrong.

WARNING: Error occurred while executing PL/pgSQL function
fn_insert_entry_pending WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near "$1" at character 58
Possibly unrelated, but what is this variable?
SELECT INTO tmp id_user FROM "user" WHERE id_user = email;

^^^

Also - make sure you haven't got a column called 'email' in any query where
you are using the variable 'email' - that can cause confusion.

--
Richard Huxton
Archonet Ltd

---------------------------(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

Nov 22 '05 #5
Hello Richard,

Thursday, January 15, 2004, 4:10:10 PM, you wrote:

RH> On Thursday 15 January 2004 14:25, Uros wrote:
Hello!

I have problem with my function and I can find what's wrong.

WARNING: Error occurred while executing PL/pgSQL function
fn_insert_entry_pending WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near "$1" at character 58
RH> Possibly unrelated, but what is this variable?
SELECT INTO tmp id_user FROM "user" WHERE id_user = email;

RH> ^^^

RH> Also - make sure you haven't got a column called 'email' in any query where
RH> you are using the variable 'email' - that can cause confusion.
Problem was aliases, becasu alias name was the same as column and then the
same name was value and rowname. I replace all names like email1 etc. and
it works. I hope ;).

--
Best regards,
Uros mailto:ur**@sir-mag.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #6
Uros <ur**@sir-mag.com> writes:
I have problem with my function and I can find what's wrong.


Don't use plpgsql variable names that conflict with field names of the
tables you are trying to access in the function.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #7
On Thursday 15 January 2004 15:22, Uros wrote:

RH> Also - make sure you haven't got a column called 'email' in any query
where RH> you are using the variable 'email' - that can cause confusion.
Problem was aliases, becasu alias name was the same as column and then the
same name was value and rowname. I replace all names like email1 etc. and
it works. I hope ;).


The error messages could be a bit clearer with some of these problems - I've
had quite a bit of experience now decoding strange parser errors with
plpgsql.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #8
Richard Huxton <de*@archonet.com> writes:
On Thursday 15 January 2004 15:22, Uros wrote:
RH> Also - make sure you haven't got a column called 'email' in any query
where RH> you are using the variable 'email' - that can cause confusion.

Problem was aliases, becasu alias name was the same as column and then the
same name was value and rowname. I replace all names like email1 etc. and
it works. I hope ;).
The error messages could be a bit clearer with some of these problems - I've
had quite a bit of experience now decoding strange parser errors with
plpgsql.


Would it help any if plpgsql showed the actual string fed to the main
SQL parser? In PG 7.4, the message you would get from Uros' example is

ERROR: syntax error at or near "$1" at character 58
CONTEXT: PL/pgSQL function "fn_insert_entry_pending" line 26 at SQL statement

but I think we could make it produce something like

ERROR: syntax error at or near "$1" at character 58
CONTEXT: PL/pgSQL function "fn_insert_entry_pending" line 26 at SQL statement "INSERT INTO "directory_entry_pending" (id_entry,id_user, $1 , $2 , $3 , $4 , $5 ,hash) VALUES ( $6 , $7 , $8 , $9 , $10 , $11 , $12 ,'1')"

I'm not sure if this would be helpful or just confusing. In particular
notice how the parameter symbols have been renumbered compared to what
was written in the function, which for reference is

INSERT INTO "directory_entry_pending" (id_entry,id_user,caption,url,is_company,is_novelt y,expire,hash) VALUES (id_entry_tmp,$1,$2,$5,$6,$7,$8,''1'');

I can see that confusing someone. But at least this would give users a
reasonable shot at understanding what happened. Right now you have to
enable log_statement and dig in the postmaster log to see what's
happening under the hood. (That's how I got the correct string to
exhibit in the example above...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: sky2070 | last post by:
Parse error: parse error, unexpected T_OBJECT_OPERATOR, expecting ')' in c:\inetpub\wwwroot\session.php on line 19 can anyone tell me what is wrong with this code??? <? // Define the Session...
2
by: Salim | last post by:
Hi people, keep getting this errorParse error: parse error, unexpected T_STRING in order_fns.php line 91. the code is below for the file and I've indicated line 91 <?php function...
1
by: H.L Bai | last post by:
hi, everybody i meet a parse error when i used the xml4c. any proposal is helpful. The error is following .../XMLRegionHandler.h:59 parse error before '*' .../XMLRegionHandler.h:60 parse...
4
by: Marcin Dobrucki | last post by:
I've been having some problems with a parse error that I can't figure out (PHP 4.3.11 on Solaris9). Sample code: <?php // getting strange parse errors on this class A { var $value; function...
6
by: Ehartwig | last post by:
I recently created a script for user verification, solved my emailing issues, and then re-created the script in order to work well with the new PHP 5 that I installed on my server. After...
0
by: jinapaia | last post by:
hi, i am getting a message Parse error:parse error, unexpected $...please help with this code: <?php require_once('db_login.php'); $sdbh = ""; $expire = 900; function sess_open($save_path,...
5
AdrianH
by: AdrianH | last post by:
Assumptions I am assuming that you know or are capable of looking up the functions I am to describe here and have some remedial understanding of C++ programming. FYI Although I have called...
1
AdrianH
by: AdrianH | last post by:
Assumptions I am assuming that you know or are capable of looking up the functions I am to describe here and have some remedial understanding of C programming. FYI Although I have called this...
2
by: Lawrence Krubner | last post by:
Imagine a template system that works by getting a file, as a string, and then putting it through eval(), something like this: $formAsString = $controller->command("readFileAndReturnString",...
9
akohistani
by: akohistani | last post by:
I am having Parse error problem with my newly purchased Php upload script I have uploaded the script and I get the error below Parse error: syntax error, unexpected $end in URL/functions.php on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.