Passing parameters to EXECUTE in plpgsql function
I get following error from the function below:
ERROR: column "_df" does not exist
create or replace function lax()returns setof record as
$$
declare
rs record;
_planunitcode int;
_DF VARCHAR(25);
str text;
begin
_planunitcode:=1;
_DF :='role.planner';
str :='select role from userplanunit where role = _DF';
--str :='select role 开发者_JS百科from userplanunit where role = quote_literal('DF');';quote_ident
for rs in execute str
--for rs in select role from userplanunit where role = _DF
loop
return next rs;
end loop;
return;
end
$$ language 'plpgsql';
i rectified it is the problem of escaping the single quote
CREATE OR replace FUNCTION lax ()
RETURNS setof record AS $$
DECLARE rs record;
_planunitcode INT;
_DF VARCHAR(25);
str TEXT;
BEGIN
_planunitcode: = 1;
_DF : = 'role.planner';
str : = 'select role from userplanunit where role ='''||_DF||'''';
FOR
rs IN
EXECUTE str LOOP
RETURN NEXT rs;
END
LOOP;
RETURN;END $$
LANGUAGE 'plpgsql';
精彩评论