Need help with EXECUTE function - Mailing list pgsql-sql

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