Thread: Query on a record variable

Hi,

How can I do a query on a record variable in a function.
I want to do a dirty fulltextsearch on a table and then choose the string which have a low levenshtein-distance.
I wanted to it like this, but it doesn't work:

v_query := 'SELECT col FROM table WHERE LENGTH(dede) BETWEEN x AND y AND plainto_tsquery(string') @@ vectors';
EXECUTE v_query INTO v_rec;
v_query :=   'SELECT levenshtein('string', v_rec.col) AS lev WHERE levenshtein('string', v_rec.col) < 10';
EXECUTE v_query INTO v_rec2;

Or is there an easier way to do that. Maybe just to do one query at once?

Hope someone can help me.

Janek Sendrowski

On 08/18/2013 01:14 PM, Janek Sendrowski wrote:
> Hi,
>
> How can I do a query on a record variable in a function.
> I want to do a dirty fulltextsearch on a table and then choose the string which have a low levenshtein-distance.
> I wanted to it like this, but it doesn't work:
>
> v_query := 'SELECT col FROM table WHERE LENGTH(dede) BETWEEN x AND y AND plainto_tsquery(string') @@ vectors';
> EXECUTE v_query INTO v_rec;
> v_query :=   'SELECT levenshtein('string', v_rec.col) AS lev WHERE levenshtein('string', v_rec.col) < 10';
> EXECUTE v_query INTO v_rec2;
>
> Or is there an easier way to do that. Maybe just to do one query at once?

http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

You can't drop the v_rec variable directly into the string. You will
need to concatenate it in. For more info see:

http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN



>
> Hope someone can help me.
>
> Janek Sendrowski
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Hi Janek,

> Hi,
>
> ok :)

I suppose you have a table 'table' with 'col' (text), 'dede' (text) and
'vectors' (tsvector) as fields. In this case, you can do

SELECT levenshtein(col, 'string') FROM table AS lev WHERE
levenshtein(col, 'string') < 10 AND LENGTH(dede) BETWEEN x AND y AND
plainto_tsquery('string') @@ vectors;

Hope it can help.

Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it