Folks (esp Jan, Tom & Michael):
I have a search function I'm testing, which uses the EXECUTE function to
perform a dynamic set of string comparisons according to whcih criteria
the user passes along. Unfortunately, this requires me to triple-nest
my quotes .... and I can't seem to get it right. No matter how I play
with the function, it keeps blowing up due to "unterminated strings".
This happens even if I terminate the function short of the EXECUTE
statement.
Can someone *please* give me some pointers?
Function text:
create function fn_search_orders ( INT4, VARCHAR, INT2, VARCHAR, DATE,
VARCHAR,INT4, INT4, VARCHAR, VARCHAR, INT4 )
RETURNS int4 AS '
DECLAREv_client ALIAS for $1;v_clname ALIAS for $2;v_status ALIAS for $3;v_datesearch ALIAS for $4;v_start ALIAS for
$5;v_addressALIAS for $6;v_contact ALIAS for $7;v_staff_usq ALIAS for $8;v_staff_name ALIAS for $9;v_temps ALIAS for
$10;v_temp_usqALIAS for $11;search_id INT4;query_string VARCHAR;where_string VARCHAR;search_count INT4;
BEGINsearch_id := NEXTVAL(''search_sq'');query_string := ''INSERT INTO searches ( search_sq, usq ) SELECT '' ||
CAST(search_id AS VARCHAR)
|| '', usq FROM sv_orders WHERE '';where_string := '''';IF v_client > 0 THEN where_string := '' AND client_usq = ''
||CAST(v_client AS varchar);END IF;IF trim(v_clname) <> '''' THEN where_string := where_string || '' AND client_name
~*'''''''' ||
v_clname ||
'''''';END IF;IF v_status <> 0 THEN where_string := where_string || '' AND status = '' || CAST(v_status AS
VARCHAR);ELSE where_string := where_string || '' AND status > 0'';END IF;IF v_start > ''1950-01-01''::DATE THEN
IFv_datesearch = ''BEFORE'' THEN where_string := where_string || '' AND start_date < '''''''' ||
to_char(v_start, ''YYYY-MM-DD'') || ''''''; ELSE where_string := where_string || '' AND start_date > ''''''''
||
to_char(v_start,
''YYYY-MM-DD'') || ''''''; END IF;END IF;IF trim(v_address) <> '''' THEN where_string := where_string || '' AND
order_address~* '''''''' ||
v_address
|| '''''';END IF;IF v_staff_usq > 0 THEN where_string := where_string || '' AND resp_staff_usq = '' ||
CAST(v_staff_usq AS VARCHAR);END IF;IF trim(v_staff) <> '''' THEN where_string := where_string || '' AND staff_name
~*'''''''' ||
v_staff ||
'''''';END IF;IF trim(v_contact) <> '''' THEN where_string := where_string || '' AND order_contact ~* '''''''' ||
v_contact
|| '''''';END IF;IF trim(v_temps) <> '''' THEN where_string := where_string || '' AND list_temps ~* '''''''' ||
v_temps ||
'''''';END IF;IF v_temp_usq > 0 THEN where_string := where_string || '' AND usq IN(SELECT order_usq FROM
assignments WHERE candidate_usq = '' || CAST(v_temp_usq AS VARCHAR) ||
'')'';END IF;where_string := substr(where_string, 5);EXECUTE query_string || where_string;SELECT count(*) INTO
search_countFROMsearches WHERE search_sq = search_id;IF search_count > 0 THEN RETURN search_id;ELSE RETURN 0;END
IF;
END;'
LANGUAGE 'plpgsql';
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco