Dynamic Query

 Hi,

I'm a novice PostgreSQL developer from an Oracle background and am trying to replicate some Oracle functionality in PostgreSQL / plpgSQL.

I'm trying to write a stored function to implement a search: the function has several parameters - the value of any could be 'null' on any given invocation, indicating that this parameter does not represent a data item being searched on.

In Oracle, this could be implemented as follows - this implementation copes with missing values and allows the user of bind variables - helping to guarantee performance and also providing protection against SQL Injection:

FUNCTION fnGetStandardUsers
(
  p_in_aur_username         IN VARCHAR2
, p_in_is_account_enabled IN VARCHAR2
)
 RETURN SYS_REFCURSOR
 IS

    l_SQL     VARCHAR2(32767 CHAR) DEFAULT    
                                               ' SELECT '
                                           || '        vsaur.aur_id                 id '
                                           || '      , vsaur.aur_username           '
                                           || '      , vsaur.aur_is_account_enabled '
                                           || '   FROM '
                                           || '        app_data.v_standard_app_user vsaur '
                                           || '  WHERE '
                                           || '        1 = 1 ';

BEGIN

  IF p_in_aur_username IS NOT NULL THEN
    l_SQL := l_SQL || ' AND vsaur.aur_username LIKE ''%''||:p_in_aur_username||''%'' ';
  ELSE
    l_SQL := l_SQL || ' AND (1 = 1 OR :p_in_aur_username IS NULL) ';
  END IF;

   OPEN 
         l_dataSet 
     FOR
         l_SQL
   USING
         UPPER(p_in_aur_username);

   RETURN l_dataSet;

END fnGetStandardUsers;

Is there a recommended way to translate this function into plpgSQL which would protect me from SQL Injection (most important for me) and use bind variables (of secondary importance?

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 Injection though???)

Any help would be appreciated!

Thanks,

Andrew



Use Windows Live Messenger for free on selected mobiles. Learn more.