From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: plpgsql, dynamic variable lengths..
Date: Fri, 07 Feb 2020 21:09:44 -0500
Message-ID: <29969.1581127784@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOC+FBVDAcHKJXfJhYVf4Et5E6ohbE+Sw4s2jG+ioCCLJOjunw@mail.gmail.com>
References: <CAOC+FBVDAcHKJXfJhYVf4Et5E6ohbE+Sw4s2jG+ioCCLJOjunw@mail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, wells.oliver@gmail.com
Subject: Re: plpgsql, dynamic variable lengths..
In-Reply-To: <29969.1581127784@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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