PL/pgSQL for all-in-one dynamic query

2019-06-10 02:01发布

问题:

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 IFs and JOINs. 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

回答1:

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:

  1. 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';
  1. 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.

  2. 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.



回答2:

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);