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
http://clk.atdmt.com/UKM/go/174426567/direct/01/