I am using PostigreSQL10. I have an all-in-one query where a user can pick a non-standard combination of category style, event, area to search for a constructions. Keep in mind that category style, event, area are in different tables.
I want to avoid the multiple IF
s and JOIN
s. I also want to avoid the query planner caching the wrong plan for the wrong parameter combination and the sequel recompilation of the query every single time. So I have to use dynamic SQL.
To get dynamic SQL in PostgreSQL, I have to use PL/pgSQL. But, according to its documentation
to generate dynamic commands inside your PL/pgSQL functions, that is,
commands that will involve different tables or different data types
each time they are executed. PL/pgSQL's normal attempts to cache plans
for commands will not work in such scenarios. To handle this sort of
problem, the EXECUTE statement is provided. Also, there is no plan
caching for commands executed via EXECUTE. Instead, the command is
always planned each time the statement is run. If multiple rows are
returned, only the first will be assigned to the INTO variable - here
and
SQL commands that appear directly in a PL/pgSQL function must refer to
the same tables and columns on every execution; that is, you cannot
use a parameter as the name of a table or column in an SQL command. To
get around this restriction, you can construct dynamic commands using
the PL/pgSQL EXECUTE statement — at the price of performing new parse
analysis and constructing a new execution plan on every execution - here
So, I guess that PL/pgSQL is not the right tool for my case, since I have multiple tables.
My question is: is PL/pgSQL actually not suitable for my case, or am I missing something ?
Sub-question: if it not suitable, how can I syntax dynamic sql for postgreSQL, I cannot find any tutorial.
Thanks
Can you please post some table definitions and a sample query of what you're trying to do? I'm not 100% sure what you're after, but there are a couple forms of "dynamic" SQL using stored procedures/functions:
- Create a function that takes input parameters (i.e. categoryType, styleId, eventName, areaId) and plug those values into a "static" SQL request. Here's a sample query snippet for your case:
SELECT *
FROM category cat
INNER JOIN style st ON cat.styleid = style.id
WHERE (cat.categoryType = pCategoryType OR pCategoryType IS NULL)
AND (st.id = pStyleId OR pStyleId IS NULL)
Here's a real example:
CREATE OR REPLACE FUNCTION SP_IGLGetItem(
pItemId INTEGER
)
RETURNS TABLE(
ItemId INTEGER,
ItemName VARCHAR(100),
ItemCategory CHAR(2)
AS
$$
BEGIN
RETURN QUERY
SELECT i.ItemId, i.ItemName, i.ItemCategory
FROM Item i
WHERE (i.ItemId = pItemId OR pItemId IS NULL) -- Return single item (if specified, otherwise return all)
;
END;
$$
LANGUAGE 'plpgsql';
Build a string containing the SQL you want to execute dynamically based on different conditions, parameter values, etc. This is as dynamic as you can get.
Conditionally run different "static" SQL statements based on the values of your input parameters.
Do any of these match your situation?
PL/PGSQL is just the language used to write stored procedures/functions in Postgres. If you really do need dynamic SQL generation, then you're best bet would be to write a function using PL/PGSQL.
Another option, is to dynamically generate the SQL you want in your client application and then just submit that SQL directly to be executed.
You can run most queries inside pl/pgsql execute.
For example, this 2 table join select will work just fine:
drop table if exists dyn_tab1;
create table dyn_tab1 (id int primary key, value text);
insert into dyn_tab1 values (1, 'test1'), (2, 'test2');
drop table if exists dyn_tab2;
create table dyn_tab2
(id serial primary key, fk_id int references dyn_tab1(id), value text);
insert into dyn_tab2 (fk_id, value)
values (1, 'blahblah'), (1, 'blahblah3'), (1, 'foobar'), (2, 'asdf');
select *
from dyn_tab1 as t1
join dyn_tab2 as t2 on t2.fk_id = t1.id;
--I'm mixing here both format and USING (prepared statement).
--You can use format for everything tho. Or just concat strings.
do $$
declare
l_row record;
begin
for l_row in
execute format($query$ select *
from %I as t1
join %I as t2 on t2.fk_id = t1.id
where t1.id = $1;
$query$, 'dyn_tab1', 'dyn_tab2')
using 2
loop
raise notice 'record: %', l_row;
end loop;
end;
$$;
create function dyn_test()
returns setof record
as $$
begin
return query execute format($query$ select *
from %I as t1
join %I as t2 on t2.fk_id = t1.id
where t1.id = $1;
$query$, 'dyn_tab1', 'dyn_tab2')
using 2;
end;
$$ language plpgsql;
select * from dyn_test() as (id int, value text, id2 int, fk int, value2 text);