parse error in function

[Uros]

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_entr y_pending"(varc har,varchar,var char,varchar,va rchar,boolean,b oolean,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(e mail) || '')'';
end if;
select into id_entry_tmp id_entry from "directory_entr y" where url=url and is_novelty=is_n ovelty;

DELETE FROM "directory_entr y_pending" WHERE "url"=url and "id_entry"=id_e ntry_tmp and "is_novelty"=is _novelty;

INSERT INTO "directory_entr y_pending" (id_entry,id_us er,caption,url, is_company,is_n ovelty,expire,h ash) 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

[John Sidney-Woollett]

Re: parse error in function

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:[color=blue]
> 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_entr y_pending"(varc har,varchar,var char,varchar,va rchar,boolean,b oolean,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(e mail) || '')'';
> end if;
> select into id_entry_tmp id_entry from "directory_entr y" where url=url
> and is_novelty=is_n ovelty;
>
> DELETE FROM "directory_entr y_pending" WHERE "url"=url and
> "id_entry"=id_e ntry_tmp and "is_novelty"=is _novelty;
>
> INSERT INTO "directory_entr y_pending"
> (id_entry,id_us er,caption,url, is_company,is_n ovelty,expire,h ash)
> 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
>[/color]


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

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

[Uros]

Re: parse error in function

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:[color=blue][color=green]
>> 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_entr y_pending"(varc har,varchar,var char,varchar,va rchar,boolean,b oolean,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(e mail) || '')'';
>> end if;
>> select into id_entry_tmp id_entry from "directory_entr y" where url=url
>> and is_novelty=is_n ovelty;
>>
>> DELETE FROM "directory_entr y_pending" WHERE "url"=url and
>> "id_entry"=id_e ntry_tmp and "is_novelty"=is _novelty;
>>
>> INSERT INTO "directory_entr y_pending"
>> (id_entry,id_us er,caption,url, is_company,is_n ovelty,expire,h ash)
>> 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
>>[/color][/color]




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[John Sidney-Woollett]

Re: parse error in function

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:[color=blue]
> Hello John,
>
> This happen when i run my function and I check for this characters. I[/color]
have[color=blue]
> only spaces.
>
> Thursday, January 15, 2004, 3:29:35 PM, you wrote:
>
> JSW> Have you got any funny characters in your function definition (like[/color]
a[color=blue]
> TAB
> JSW> character) - I can't tell from your e-mail.
>
> JSW> I've seen problems before inserting function using psql when this[/color]
is[color=blue]
> the
> JSW> case.
>
> JSW> John Sidney-Woollett
>
> JSW> Uros said:[color=green][color=darkred]
>>> 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_entr y_pending"(varc har,varchar,var char,varchar,va rchar,boolean,b oolean,date,int ,int)[/color][/color][/color]
returns integer as '[color=blue][color=green][color=darkred]
>>> 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[/color][/color][/color]
found then[color=blue][color=green][color=darkred]
>>> execute ''INSERT INTO "user" (id_user) VALUES ('' ||
>>> quote_literal(e mail) || '')'';
>>> end if;
>>> select into id_entry_tmp id_entry from "directory_entr y" where
>>> url=url
>>> and is_novelty=is_n ovelty;
>>> DELETE FROM "directory_entr y_pending" WHERE "url"=url and
>>> "id_entry"=id_e ntry_tmp and "is_novelty"=is _novelty;
>>> INSERT INTO "directory_entr y_pending"
>>> (id_entry,id_us er,caption,url, is_company,is_n ovelty,expire,h ash)[/color][/color][/color]
VALUES (id_entry_tmp,$ 1,$2,$5,$6,$7,$ 8,''1'');[color=blue][color=green][color=darkred]
>>> return 1;
>>> end;
>>> ' language 'plpgsql';
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 7: don't forget to increase your free space map settings[/color][/color]
>
>
>
>
> ---------------------------(end of broadcast)---------------------------[/color]
TIP 2: you can get off all lists at once with the unregister command[color=blue]
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)
>[/color]




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

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

[Richard Huxton]

Re: parse error in function

On Thursday 15 January 2004 14:25, Uros wrote:[color=blue]
> 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[/color]

Possibly unrelated, but what is this variable?
[color=blue]
> SELECT INTO tmp id_user FROM "user" WHERE id_user = email;[/color]
^^^

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

[Uros]

Re: parse error in function

Hello Richard,

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

RH> On Thursday 15 January 2004 14:25, Uros wrote:[color=blue][color=green]
>> 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[/color][/color]

RH> Possibly unrelated, but what is this variable?
[color=blue][color=green]
>> SELECT INTO tmp id_user FROM "user" WHERE id_user = email;[/color][/color]
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:uros@sir-mag.com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: parse error in function

Uros <uros@sir-mag.com> writes:[color=blue]
> I have problem with my function and I can find what's wrong.[/color]

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

[Richard Huxton]

Re: parse error in function

On Thursday 15 January 2004 15:22, Uros wrote:[color=blue]
>
> 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 ;).[/color]

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: parse error in function

Richard Huxton <dev@archonet.c om> writes:[color=blue]
> On Thursday 15 January 2004 15:22, Uros wrote:[color=green]
>> 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 ;).[/color][/color]
[color=blue]
> 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.[/color]

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_entr y_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_entr y_pending" line 26 at SQL statement "INSERT INTO "directory_entr y_pending" (id_entry,id_us er, $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_entr y_pending" (id_entry,id_us er,caption,url, is_company,is_n ovelty,expire,h ash) 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