Thread: plpgsql, dynamic variable lengths..
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
Wells Oliver <wells.oliver@gmail.com> writes:
> I am trying to create a plpgsql function which accepts a jsonb object as
> its parameter.
> 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?
I think your USING is probably fine. But you made all the SQL fragments
refer to $1:
> 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;
Of course those need to be $1, $2, $3.
regards, tom lane
Yes, but the issue is it won't always be three params, and not always the same variable in $1, $2, etc. It depends on which keys are present in the passed JSON object. Which I why I think I need to do something else like create a recordset and pass that in USING, or something. Just can't quite figure it out...
On Fri, Feb 7, 2020 at 6:09 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> I am trying to create a plpgsql function which accepts a jsonb object as
> its parameter.
> 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?
I think your USING is probably fine. But you made all the SQL fragments
refer to $1:
> 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;
Of course those need to be $1, $2, $3.
regards, tom lane
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes:
> Yes, but the issue is it won't always be three params, and not always the
> same variable in $1, $2, etc. It depends on which keys are present in the
> passed JSON object. Which I why I think I need to do something else like
> create a recordset and pass that in USING, or something. Just can't quite
> figure it out...
Maybe just pass the JSON object as the only USING param, and do the
field selections in the SQL fragments?
regards, tom lane
Le sam. 8 févr. 2020 à 03:36, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Wells Oliver <wells.oliver@gmail.com> writes:
> Yes, but the issue is it won't always be three params, and not always the
> same variable in $1, $2, etc. It depends on which keys are present in the
> passed JSON object. Which I why I think I need to do something else like
> create a recordset and pass that in USING, or something. Just can't quite
> figure it out...
Maybe just pass the JSON object as the only USING param, and do the
field selections in the SQL fragments?
And why don't you do this instead?
SQLSTRING := SQLSTRING || ' AND col1 = ' || quote_literal(args->>'col1');
--
Guillaume.
Beaut, that's it.
I still can't tell if this is too hacky to bear, but it works. Trying to essentially use a python-style kwargs declaration, but with a jsonb object. We'll see.
Thanks!
On Sat, Feb 8, 2020 at 12:54 AM Guillaume Lelarge <guillaume@lelarge.info> wrote:
Le sam. 8 févr. 2020 à 03:36, Tom Lane <tgl@sss.pgh.pa.us> a écrit :Wells Oliver <wells.oliver@gmail.com> writes:
> Yes, but the issue is it won't always be three params, and not always the
> same variable in $1, $2, etc. It depends on which keys are present in the
> passed JSON object. Which I why I think I need to do something else like
> create a recordset and pass that in USING, or something. Just can't quite
> figure it out...
Maybe just pass the JSON object as the only USING param, and do the
field selections in the SQL fragments?And why don't you do this instead?SQLSTRING := SQLSTRING || ' AND col1 = ' || quote_literal(args->>'col1');
--Guillaume.
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com