plpgsql, dynamic variable lengths..

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

plpgsql, dynamic variable lengths..

Wells Oliver-2
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?

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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: plpgsql, dynamic variable lengths..

Tom Lane-2
Wells Oliver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: plpgsql, dynamic variable lengths..

Wells Oliver-2
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 <[hidden email]> wrote:
Wells Oliver <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: plpgsql, dynamic variable lengths..

Tom Lane-2
Wells Oliver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: plpgsql, dynamic variable lengths..

Guillaume Lelarge-3
Le sam. 8 févr. 2020 à 03:36, Tom Lane <[hidden email]> a écrit :
Wells Oliver <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: plpgsql, dynamic variable lengths..

Wells Oliver-2
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 <[hidden email]> wrote:
Le sam. 8 févr. 2020 à 03:36, Tom Lane <[hidden email]> a écrit :
Wells Oliver <[hidden email]> 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
[hidden email]