postgresql - Initialize a string in a stored function -

i call procedure without problem :

select sp_getglobalvariable('current_user_id')::int ; 

if call inside stored procedure :

-- current user id -- select sp_getglobalvariable('current_user_id')::int __currentuserid ; 

i have error :

error : syntax error near "current_user_id" line 25:   select sp_getglobalvariable('current_user_id')::int _... 

it stupid syntax error... if can poor mysql user starting using pg !

edit 1 :

select version(); postgresql 9.3.3 on i686-pc-linux-gnu, compiled gcc-4.4.real (debian 4.4.5-8) 4.4.5, 32-bit 

this result of version() call.

create or replace function sp_getglobalvariable (__variablename varchar(64)) returns varchar '  declare  begin      return  (select value tmp_variables             name = __variablename) ;  end ; ' language 'plpgsql'; 

the procedure global variable in temporary table.*

edit 2 :

create or replace function sp_insertsite (__sitename varchar(70), __sitedescription  text, __sitelatitude numeric, __sitelongitude numeric) returns int '  declare  -- variables __right_edge_father int ; __left_edge_father int ; __depth_father int ; __nbrsites int ; __insertid int ; __currentuserid int ;  begin      -- check if tree empty --     select count(*) __nbrsites     site ;      -- current user id --     select sp_getglobalvariable('current_user_id')::int __currentuserid ;      if __nbrsites = 0          insert site (site_name, site_description, site_latitude, site_longitude, site_left_edge, site_right_edge, site_depth, site_create_dt, site_create_user_id)         values(__sitename, __sitedescription, 0.0, 0.0, 1, 2, 0, localtimestamp, __currentuserid)         returning site_id __insertid;      else          -- father edges --         select site_left_edge, site_right_edge, site_depth         __left_edge_father, __right_edge_father, __depth_father         site         site_id = (select site_id site site_depth = 0 limit 1) ;          -- updates left edges --         update site         set site_left_edge = site_left_edge + 2         site_left_edge >= __right_edge_father ;          -- updates right edges --         update site         set site_right_edge = site_right_edge + 2         site_right_edge >= __right_edge_father ;          -- insert new node --         insert site (site_name, site_description, site_latitude, site_longitude, site_left_edge, site_right_edge, site_depth, site_create_dt, site_create_user_id)         values(__sitename, __sitedescription, __sitelatitude, __sitelongitude, __right_edge_father, __right_edge_father+1, __depth_father+1, localtimestamp, __currentuserid)         returning site_id __insertid;      end if ;      return __insertid ;  end ; ' language plpgsql; 

the problem using single quotes encapsulate function body, , so, when try add single quotes inside body, need escape it, instance:

create or replace function sp_insertsite (...) returns int '  declare ...     -- current user id --     select sp_getglobalvariable(''current_user_id'')::int __currentuserid ; ...     return __insertid ;  end ; ' language plpgsql; 

that work nice. recommend use dollar-quoting , don't have worry single-quote escaping inside function body, following:

create or replace function sp_insertsite (...) returns int $$ declare ...     -- current user id --     select sp_getglobalvariable('current_user_id')::int __currentuserid ; ...     return __insertid ;  end ; $$ language plpgsql; 

i'd recommend read postgresql docs usage of dollar-quoting in pl/pgsql (the entire document, not sub-section interesting read).