pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql, dynamic variable lengths..
6+ messages / 3 participants
[nested] [flat]
* plpgsql, dynamic variable lengths..
@ 2020-02-08 00:38 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Wells Oliver @ 2020-02-08 00:38 UTC (permalink / raw)
To: pgsql-admin
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
wells.oliver@gmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql, dynamic variable lengths..
@ 2020-02-08 02:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-02-08 02:09 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql, dynamic variable lengths..
@ 2020-02-08 02:23 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Wells Oliver @ 2020-02-08 02:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql, dynamic variable lengths..
@ 2020-02-08 02:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-02-08 02:36 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql, dynamic variable lengths..
@ 2020-02-08 08:54 Guillaume Lelarge <guillaume@lelarge.info>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Guillaume Lelarge @ 2020-02-08 08:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: plpgsql, dynamic variable lengths..
@ 2020-02-08 20:41 Wells Oliver <wells.oliver@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 6+ messages in thread
From: Wells Oliver @ 2020-02-08 20:41 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-02-08 20:41 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-08 00:38 plpgsql, dynamic variable lengths.. Wells Oliver <wells.oliver@gmail.com>
2020-02-08 02:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-02-08 02:23 ` Wells Oliver <wells.oliver@gmail.com>
2020-02-08 02:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-02-08 08:54 ` Guillaume Lelarge <guillaume@lelarge.info>
2020-02-08 20:41 ` Wells Oliver <wells.oliver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox