Variables not recognized in PostgreSQL function subquery?

Question

Working solution:

CREATE OR REPLACE FUNCTION my_search(tsq tsquery, translation_id integer, lang regconfig, count integer DEFAULT 10, skip integer DEFAULT 0)
RETURNS TABLE(id int, chapter int, number int, headline text) AS $$
      SELECT id, chapter, number, ts_headline($3, text, $1, 'StartSel = <em>, StopSel = </em>'::text) FROM (
          SELECT id, chapter, number, text FROM lyrics
          WHERE $1 @@ search_text AND translation_id = $2
          LIMIT $4 OFFSET $5) AS matches;
$$ LANGUAGE SQL STABLE;

Original question below.

---

I have been trying to create a PostgreSQL stored procedure like this:

CREATE OR REPLACE FUNCTION my_search(tsq tsquery, translation_id integer, lang text, count integer DEFAULT 10, skip integer DEFAULT 0)
RETURNS TABLE(id int, chapter int, number int, headline text) AS $$
      SELECT id, chapter, number, ts_headline(lang, text, tsq, 'StartSel = <em>, StopSel = </em>') FROM (
          SELECT (id, chapter, number, text) FROM my_texts
          WHERE tsq @@ search_text AND translation_id = translation_id
          LIMIT count OFFSET skip) AS matches;
$$ LANGUAGE SQL STABLE;

But when I try to load it into the database, I get this error:

psql:scriptura.pgsql:7: ERROR:  column "tsq" does not exist
LINE 5:           WHERE tsq @@ search_text AND translation_id = tran...
                        ^

It seems that the function variables does somehow not come into scope for the subquery. I've been scouring the PostgreSQL docs, and I can't seem to find out why. Can anyone figure out what happens to my variable?

Answer 1

LANGUAGE SQL only accepts positional parameters:

CREATE OR REPLACE FUNCTION my_search(tsq tsquery, translation_id integer, lang text, count integer DEFAULT 10, skip integer DEFAULT 0)
RETURNS TABLE(id int, chapter int, number int, headline text) AS $$
      SELECT id, chapter, number, ts_headline($3, text, $1, 'StartSel = <em>, StopSel = </em>') FROM (
          SELECT (id, chapter, number, text) FROM my_texts
          WHERE $1 @@ search_text AND translation_id = $2
          LIMIT $4 OFFSET $5) AS matches;
$$ LANGUAGE SQL STABLE;

From the documentation:

Arguments to the SQL function are referenced in the function body using the syntax $n: $1 refers to the first argument, $2 to the second, and so on. If an argument is of a composite type, then the dot notation, e.g., $1.name, can be used to access attributes of the argument.