Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f37L9V824899 for ; Sat, 7 Apr 2001 17:09:31 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 35393; Sat, 07 Apr 2001 14:09:06 -0700 From: "Josh Berkus" Subject: Need help with EXECUTE function To: pgsql-sql@postgresql.org Cc: jeff@hub.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Sat, 07 Apr 2001 14:09:06 -0700 Message-ID: In-Reply-To: <3ACF770F.D5E65105@actarg.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/104 X-Sequence-Number: 2444 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 ' DECLARE v_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_address ALIAS 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_usq ALIAS for $11; search_id INT4; query_string VARCHAR; where_string VARCHAR; search_count INT4; BEGIN search_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 IF v_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_count FROM searches 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 information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco