How to set a variable in a postgresSQL function, to a computed value from another function-CodePudding

Im having a bit of trouble figuring this one out.

Essentially id like to bind the computed value of websearch_to_tsquery('simple', search_term) to variable foo.

A hack would be to just hardcode the to_tsquery where needed but i wanted to try and clean up my code a bit.

I am reciving the following error,

ERROR:  syntax error at or near "foo"
LINE 8: foo tsquery := websearch_to_tsquery('simple', search_term);

here is my code

DROP FUNCTION search_exercises(text);
CREATE OR REPLACE FUNCTION search_exercises(search_term text)
returns setof "Exercise"
language plpgsql
as 
$$
BEGIN
  foo tsquery := websearch_to_tsquery('simple', search_term)
  SELECT * FROM "Exercise"
  WHERE foo @@ document
  ORDER BY ts_rank("document", foo) DESC
END;
$$;

I am expecting the search_exercises(search_term) function to return a table of Exercise ranked descending by ts_rank given a document and that tsquery computed from the input arg search_term

Ty!

How to set the value of a variable in one function with the returned value from another function?

Select integer returning function result into variable postgres

CodePudding user response：

You need to declare a variable before you can use it. Additionally each statement needs to be ended with a ;

as
$$
declare
  foo ts_query;
begin
  foo := websearch_to_tsquery('simple', search_term);
  ...
end;
$$

Note that your SELECT also needs a ; at the end and you need return query select ... to return the result of a query.

But you don't really need a variable, you could also do this in a single statement:

return query
  SELECT * 
  FROM "Exercise"
  WHERE websearch_to_tsquery('simple', search_term) @@ document
  ORDER BY ts_rank("document", foo) DESC;