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;
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:
And if the LOOP just has to return the results, you may just as well avoid it and return the query directly:
EDIT: Since the operator is
ltree ~ lquery
and~
binds tighter than||
, the right operand should be parenthesized and cast tolquery
: