plpgsql, dynamic variable lengths..

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: plpgsql, dynamic variable lengths..
Date: 2020-02-08 00:38:12
Message-ID: CAOC+FBVDAcHKJXfJhYVf4Et5E6ohbE+Sw4s2jG+ioCCLJOjunw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)oliver(at)gmail(dot)com

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2020-02-08 02:09:44 Re: plpgsql, dynamic variable lengths..
Previous Message sami lekesiz 2020-02-07 08:58:39 Re: PGAdmin4 as Desktop Application