I'm a postgres noob, so I am looking for advice/ comments.
My example code here demonstrates the solution to a real world situation where I am converting a PL/SQL Package (Oracle) to a pgPL/SQL function. The original package constructs an elaborate SELECT statement which can look extremely different depending on the the values of various parameters and the results of table lookups. The only thing that does not vary are the columns of the constructed SELECT statement.
fbind will be called by the function shown immediately after this
CREATE OR REPLACE FUNCTION fbind(
IN p_psudo_datatype_i character varying,
IN p_parameter_position_i int)
RETURNS text AS
$BODY$
BEGIN
CASE p_psudo_datatype_i
WHEN 'cvs num' THEN
return '= ANY ((''{''||$'||p_parameter_position_i::text||'||''}'')::bigint[] ) '::text;
WHEN 'bigint' THEN
return '= TO_NUMBER($'||p_parameter_position_i::text||',''99999999999999999999'') '::text;
ELSE
return 'datatype not implemented'::text;
END CASE;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
test driver procedure
DO $$
DECLARE
sql text := 'select vendor_name from tx_vendor WHERE active_flag = ''Y'' ';
v_bind_values text[];
v_vendor_key bigint := 1017;
v_cvs_code2tcode character varying (30) := '2222,5235,7979';
v_vendor_name text;
BEGIN
v_bind_values := array_append(v_bind_values, v_vendor_key::text);
sql := sql || '
and vendor_key'||fbind('bigint'::text,array_length(v_bind_values,1));
-- in a real use situation, the following 2 lines of code may or may not be
-- executed, meaning, we will not know how many bind variables
v_bind_values := array_append(v_bind_values,v_cvs_code2tcode::text);
sql := sql || '
and code2tcode'||fbind('cvs num'::text,array_length(v_bind_values,1));
case array_length(v_bind_values,1)
when 1 then
execute sql into strict v_vendor_name using v_bind_values[1];
when 2 then
execute sql into strict v_vendor_name using v_bind_values[1], v_bind_values[2];
else
raise exception 'undefined number of bind variables' ;
end case;
raise notice '
sql:%', sql ;
raise notice '
v_vendor_name:%',v_vendor_name;
END$$;
RUN results ... shows the SQL statement that was constructed
NOTICE:
sql:select vendor_name from tx_vendor WHERE active_flag = 'Y'
and vendor_key= TO_NUMBER($1,'99999999999999999999')
and code2tcode= ANY (('{'||$2||'}')::bigint[] )
NOTICE:
v_vendor_name:Irwin Union Bank
Query returned successfully with no result in 15 msec.
All comments or suggestions for improvement are welcome.
Regards,
Mike