On Tue, 21 Sep 2004, CHRIS HOOVER wrote: > Thanks a bunch for the pointers and help. > > One other hopefully quick question. > > How do you query using a variable containing the query? > > I'm trying to build a select statment based upon what parameters are being > passed to the function. > > somthing like this: > > Declare > Param1 varchar; > Param2 varchar; > SQLStr varchar; > Table_rec Table%ROWTYPE; > Begin > > SQLStr:="select * from table" > Param1:= $1; > Param2 :=$2; > > if (Param1 is not null) then > SQLStr := SQLStr || "where column=Param1"; > else > SQLStr := SQLStr || "where column=Param2"; > end if; > SQLStr := SQLStr || ";" > > for Table_Rec in SQLStr loop > return next Table_rec; > end loop; > return; > > end; > > Is this possible?
Pretty much yes. You can use the FOR <record> IN EXECUTE <sqlstring> LOOP structure to run the query. The only thing is that you have to put the values into the string not the name of the parameters (probably using quote_literal). So rather than SQLStr := SQLStr || "where column = Param1"; you'd want something like: SQLStr := SQLStr || "where column = " || quote_literal(Param1); ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings