Passing parameters to EXECUTE in plpgsql function

Question

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

Rock · Accepted Answer · 2012-10-19 23:47:20Z

1

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

edited Oct 19, 2012 at 23:47

Rock

2,9778 gold badges38 silver badges47 bronze badges

answered Jan 20, 2011 at 9:43

kallem

8212 gold badges9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kallem Over a year ago

THANK YOU i got it and using in my project

Collectives™ on Stack Overflow

Passing parameters to EXECUTE in plpgsql function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related