text search query building in postgres

Question

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('search_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(search_terms, '|'), without quotes because it's not a string, it's a variable. — Frank Heikens
– Frank Heikens, Commented Jun 4, 2010 at 7:40

Frank Heikens · Accepted Answer · 2010-06-04 05:30:41Z

1

Use array_to_string().

answered Jun 4, 2010 at 5:30

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

text search query building in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related