Обсуждение: sintax error
Hi there,
i have got the followed error in the pgsql function bellow.
Log messages point to:
********************
ERROR: syntax error at end of input
LINE 1: SELECT ^
QUERY: SELECT
CONTEXT: SQL statement in PL/PgSQL function
"cms_permission__revoke_permission" near line 67
********** Error **********
ERROR: syntax error at end of input
SQL state: 42601
Context: SQL statement in PL/PgSQL function
"cms_permission__revoke_permission" near line 67
********************
However, I couldn't find any sintax error in those SELECT statements
does anyone have any idea what is wrong with it?
cheers,
iuri
-- procedure revoke_permission
create or replace function cms_permission__revoke_permission
(integer,integer,varchar,integer,varchar)
returns integer as '
declare p_item_id alias for $1; p_holder_id alias for $2; p_privilege
alias for $3; p_revokee_id alias for $4; p_is_recursive alias for
$5; -- default ''f'' -- v_items item_array_type; v_item_id
cr_items.item_id%TYPE;v_idx integer; v_count integer; -- v_perms
perm_array_type; v_perm acs_privileges.privilege%TYPE; v_perm_idx
integer; v_perm_count integer; c_perm_cur record; c_item_cur
record;
begin
PERFORM update_permissions(p_item_id, p_is_recursive);
-- Select the child permissions v_perm_count := 0; for c_perm_cur in select child_privilege from
acs_privilege_hierarchy where privilege = p_privilege and child_privilege <> p_privilege LOOP
v_perm := c_perm_cur.child_privilege; v_perm_count := v_perm_count + 1; -- v_perms(v_perm_count) := v_perm;
updatev_perms set value[v_perm_count] = v_perm; end LOOP;
-- Select child items v_count := 0; for c_item_cur in select c1.item_id from cr_items c1, cr_items c2
wherec2.item_id = p_item_id and c1.tree_sortkey between c2.tree_sortkey and
tree_right(c2.tree_sortkey) and cms_permission__has_revoke_authority ( item_id, p_holder_id,
p_privilege, p_revokee_id ) = ''t'' and acs_permission__permission_p ( item_id,
p_revokee_id, p_privilege ) = ''t'' LOOP v_item_id := c_item_cur.item_id; v_count :=
v_count+ 1; -- v_items(v_count) := v_item_id; update v_items set value[v_count] = v_item_id; exit when
p_is_recursive= ''f''; end loop;
if v_count < 1 then return; end if;
-- Grant child permissions for v_idx in 1..v_count loop for v_perm_idx in 1..v_perm_count loop PERFORM
acs_permission__grant_permission( -- v_items(v_idx), p_revokee_id, v_perms(v_perm_idx)
v_items.value[v_idx],p_revokee_id, v_perms.value[v_perm_idx] ); end loop; end loop;
-- Revoke the parent permission for v_idx in 1..v_count loop PERFORM acs_permission__revoke_permission (
--v_items(v_idx), v_items.value[v_idx], p_revokee_id, p_privilege ); end loop;
return 0;
end;' language 'plpgsql';
On 19/02/10 07:52, iuri de araujo sampaio wrote: > Hi there, > i have got the followed error in the pgsql function bellow. Log messages > point to: > > ******************** > ERROR: syntax error at end of input > LINE 1: SELECT > ^ > QUERY: SELECT > CONTEXT: SQL statement in PL/PgSQL function > "cms_permission__revoke_permission" near line 67 > > ********** Error ********** > > ERROR: syntax error at end of input > SQL state: 42601 > Context: SQL statement in PL/PgSQL function > "cms_permission__revoke_permission" near line 67 > > ******************** > > However, I couldn't find any sintax error in those SELECT statements > does anyone have any idea what is wrong with it? I thought it was the PERFORM statements, but it's not. The line number seems right, although the error message is misleading. > if v_count < 1 then> return;> end if; That bare return isn't legal. Try "return NULL" instead. The "error at end of input" means it was looking for an expression to return and couldn't find one. Oh - unrelated tips. 1. You can have multi-line comments with /* ... */ - very useful for debugging. 2. Any version of PostgreSQL from the last few years supports "dollar quoting". http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS That lets you do: create or replace function ... as $$ ... IF myvar = 'normal-quoting works here' THEN ... $$ language plpgsql; -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> I thought it was the PERFORM statements, but it's not. The line number
> seems right, although the error message is misleading.
FWIW, things are better in HEAD:
regression=# create function foo() returns int as $$
regression$# begin
regression$# return;
regression$# end$$ language plpgsql;
ERROR: missing expression at or near ";"
LINE 3: return; ^
regression=#
regards, tom lane
Hi All, I am using postgres sql. At present whenever there is any error in syntax of fired query i get error message in terms of charcter "at char 53 " I also would like to have line no: from psql terminal.. I tried making changes in scan.l Do u have any suggestive fix ? to get error as "at char 54 line no 2" Regards, keval -- View this message in context: http://postgresql.1045698.n5.nabble.com/sintax-error-tp2154517p5632590.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
kevalshah <keval.shah29@gmail.com> writes:
> At present whenever there is any error in syntax of fired query
> i get error message in terms of charcter "at char 53 "
> I also would like to have line no: from psql terminal..
Umm ... any reasonably recent version of psql will show syntax errors
like this:
regression=# select 1/ from foo;
ERROR: syntax error at or near "from"
LINE 1: select 1/ from foo; ^
If you're using something so old that it doesn't do that, the answer
is to update.
regards, tom lane