Using ltree query in plpgsql function as param in PostgreSQL

Question

I want to execute query with ltree param in plpgsql function... but i can`t understand how to use quotes in this functions...

CREATE OR REPLACE FUNCTION f_select(BIGINT) RETURNS setof categories AS 
$$
DECLARE
    s_cat ALIAS FOR $1;

    queryText TEXT;
    result categories%ROWTYPE;

BEGIN
    queryText := 'SELECT * FROM categories WHERE cat_tree ~ \'*.\'|| s_cat::text ||\'.*\'';

    FOR result IN EXECUTE queryText
    LOOP
        RETURN NEXT result;
    END LOOP;

    RETURN;

END
$$
LANGUAGE plpgsql;

How to do this ???

After executing this code in psql I get error:

ERROR:  syntax error at or near "."
LINE 10: ... := 'SELECT * FROM categories WHERE cat_tree ~ \'*.\'|| s_ca...

Final working verison:

CREATE OR REPLACE FUNCTION f_select(BIGINT) RETURNS setof categories AS 
$$
DECLARE
    s_cat ALIAS FOR $1;

    queryText TEXT;
    result categories%ROWTYPE;

BEGIN

    queryText := 'SELECT * FROM categories WHERE cat_tree ~ ''' || ('*.'|| s_cat::text || '.*')::lquery || '''';

    FOR result IN EXECUTE queryText
    LOOP
        RETURN NEXT result;
    END LOOP;

    RETURN;

END
$$
LANGUAGE plpgsql;

Thanks, but it don`t help me, I try out it before you comment buuut... — dezconnect
Ok, can you provide table structure and some example data for example as sql fiddle (sqlfiddle.com)? — zero323
The way to quote a single quote is to prepend another single quote. Don't bother with backslashes, they're deprecated. — Daniel Vérité

Daniel Vérité Daniel Vérité · Accepted Answer · 2013-09-10T16:39:41

The problem seems to be misusing the backslash, but anyway putting this query into a text variable shouldn't be needed in the first place.

What about this form:

FOR result IN SELECT * FROM categories WHERE cat_tree ~ '*.'|| s_cat::text || '.*'
LOOP
    RETURN NEXT result;
END LOOP;

And if the LOOP just has to return the results, you may just as well avoid it and return the query directly:

 RETURN QUERY SELECT * FROM categories WHERE cat_tree ~ '*.'|| s_cat::text || '.*';

EDIT: Since the operator is ltree ~ lquery and ~ binds tighter than ||, the right operand should be parenthesized and cast to lquery:

 RETURN QUERY SELECT * FROM categories
   WHERE cat_tree ~ ('*.'|| s_cat::text || '.*')::lquery;

Using ltree query in plpgsql function as param in PostgreSQL

1 Answers