Dynamic Query - Mailing list pgsql-sql

Hi,<br /><br />I'm a novice PostgreSQL developer from an Oracle background and am trying to replicate some Oracle
functionalityin PostgreSQL / plpgSQL.<br /><br />I'm trying to write a stored function to implement a search: the
functionhas several parameters - the value of any could be 'null' on any given invocation, indicating that this
parameterdoes not represent a data item being searched on.<br /><br />In Oracle, this could be implemented as follows -
thisimplementation copes with missing values and allows the user of bind variables - helping to guarantee performance
andalso providing protection against SQL Injection:<br /><br />FUNCTION fnGetStandardUsers<br />(<br /> 
p_in_aur_username        IN VARCHAR2<br />, p_in_is_account_enabled IN VARCHAR2<br />)<br /> RETURN SYS_REFCURSOR<br />
IS<br/><br />    l_SQL     VARCHAR2(32767 CHAR) DEFAULT    <br />                                               '
SELECT'<br />                                           || '        vsaur.aur_id                 id '<br
/>                                          || '      , vsaur.aur_username           '<br
/>                                          || '      , vsaur.aur_is_account_enabled '<br
/>                                          || '   FROM '<br />                                           || '       
app_data.v_standard_app_uservsaur '<br />                                           || '  WHERE '<br
/>                                          || '        1 = 1 ';<br /><br />BEGIN<br /><br />  IF p_in_aur_username IS
NOTNULL THEN<br />    l_SQL := l_SQL || ' AND vsaur.aur_username LIKE ''%''||:p_in_aur_username||''%'' ';<br /> 
ELSE<br/>    l_SQL := l_SQL || ' AND (1 = 1 OR :p_in_aur_username IS NULL) ';<br />  END IF;<br /><br />   OPEN <br
/>        l_dataSet <br />     FOR<br />         l_SQL<br />   USING<br />         UPPER(p_in_aur_username);<br /><br
/>  RETURN l_dataSet;<br /><br />END fnGetStandardUsers;<br /><br />Is there a recommended way to translate this
functioninto plpgSQL which would protect me from SQL Injection (most important for me) and use bind variables (of
secondaryimportance?<br /><br />The postgresql documentation seems to suggest that I can use the RETURN QUERY EXECUTE
feature,or simply build my query with a string and execute it (I don't see how the latter can protect me from SQL
Injectionthough???)<br /><br />Any help would be appreciated!<br /><br />Thanks,<br /><br />Andrew<br /><br /><br /><br
/><hr/>Use Windows Live Messenger for free on selected mobiles. <a
href="http://clk.atdmt.com/UKM/go/174426567/direct/01/"target="_new">Learn more.</a>