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: | Whole Thread | Raw Message | 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
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 |