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?

I'd revert the title back so that the people would able to find it on Google. This is more about positional parameters than fulltext searching :) — Quassnoi
– Quassnoi, Commented Jan 12, 2010 at 18:52

Quassnoi · Accepted Answer · 2010-01-12 14:24:42Z

3

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.

answered Jan 12, 2010 at 14:24

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

1 Comment

mikl Over a year ago

Ah, that was it, thank you. I'll just update the question with a more interesting title for those who might be interested in a stored procedure for searching :)

Collectives™ on Stack Overflow

Variables not recognized in PostgreSQL function subquery?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related