plpgsql, dynamic variable lengths..
| От | Wells Oliver |
|---|---|
| Тема | plpgsql, dynamic variable lengths.. |
| Дата | |
| Msg-id | CAOC+FBVDAcHKJXfJhYVf4Et5E6ohbE+Sw4s2jG+ioCCLJOjunw@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: plpgsql, dynamic variable lengths..
|
| Список | pgsql-admin |
I am trying to create a plpgsql function which accepts a jsonb object as its parameter.
begin
SQLSTRING := 'SELECT * FROM mytable WHERE true';
if args->>'col1' is not null then
SQLSTRING := SQLSTRING || ' AND col1 = $1';
end if;
if args->>'col2' is not null then
SQLSTRING := SQLSTRING || ' AND col2 = $1';
end if;
if args->>'col3' is not null then
SQLSTRING := SQLSTRING || ' AND col3 = $1';
end if;
SQLSTRING := SQLSTRING || ' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10';
raise notice 'SQL: %', SQLSTRING;
return query execute SQLSTRING USING args->>'col1', args->>'col2', args->>'col3';
end;
$$ language plpgsql;
--
Wells Oliver
wells.oliver@gmail.com
This jsonb can have up to three keys, and I am trying to build out the dynamic SQL like this. I want to be able to pass a variable number of params to the RETURN QUERY EXECUTE ... USING ... because it can be 0 params in the JSONB object, or one, or two.
What's the missing piece here? How can I adjust my USING to accomplish this?
begin
SQLSTRING := 'SELECT * FROM mytable WHERE true';
if args->>'col1' is not null then
SQLSTRING := SQLSTRING || ' AND col1 = $1';
end if;
if args->>'col2' is not null then
SQLSTRING := SQLSTRING || ' AND col2 = $1';
end if;
if args->>'col3' is not null then
SQLSTRING := SQLSTRING || ' AND col3 = $1';
end if;
SQLSTRING := SQLSTRING || ' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10';
raise notice 'SQL: %', SQLSTRING;
return query execute SQLSTRING USING args->>'col1', args->>'col2', args->>'col3';
end;
$$ language plpgsql;
--
Wells Oliver
wells.oliver@gmail.com
В списке pgsql-admin по дате отправления: