开发者

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';
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜