text search query building in postgres
I need to build a stored procedure that takes input an array of varchars . It will search for these using syntax like
SELECT *
FROM mytable
WHERE search_index @@ to_tsquery(' ');
If i give input to the procedure like tom ,dick,harry the query should be dynamically build like
SELECT *
FROM mytable
WHERE search_index @@ to_tsquery('tom | dick | harry ');
CREATE OR REPLACE FUNCTION text_search(search_terms character varying[], ent_id bigint)
RETURNS entity_base AS
$BODY$DECLARE
result_data entity_base;
search_data text;
BEGIN
search_data := array_to_string('sea开发者_Go百科rch_terms[]', '|');
SELECT * INTO result_data FROM entity_base WHERE search_index @@ to_tsquery(search_data) and entity_id = ent_id;
RETURN result_data;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
When i run this im getting error
ERROR: could not determine polymorphic type because input has type "unknown"
CONTEXT: SQL statement "SELECT array_to_string('search_terms[]', '|')"
PL/pgSQL function "text_search" line 5 at assignment
********** Error **********
ERROR: could not determine polymorphic type because input has type "unknown"
SQL state: 42804
Context: SQL statement "SELECT array_to_string('search_terms[]', '|')"
PL/pgSQL function "text_search" line 5 at assignment
Use array_to_string().
精彩评论