Using a prepared statement in a stored function

Question

I have a table in the database:

create table store ( ... n_status integer not null, t_tag varchar(4) t_name varchar, t_description varchar, dt_modified timestamp not null, ... ); 

In my saved function, I need to execute the same select several times on this table:

 select * from store where n_place_id = [different values] and t_tag is not null and n_status > 0 and (t_name ~* t_search or t_description ~* t_search) order by dt_modified desc limit n_max; 

Here t_search and n_max are parameters in the stored function. I thought it made sense to use a prepared expression for this, but I am having strange problems. Here is what I have:

 create or replace function fn_get_data(t_search varchar, n_max integer) returns setof store as $body$ declare resulter store%rowtype; mid integer; begin prepare statement prep_stmt(integer) as select * from store where n_place_id = $1 and (t_name ~* t_search or t_description ~* t_search) order by dt_modified limit n_max; for mid in (select n_place_id from ... where ...) loop for resulter in execute prep_stmt(mid) loop return next resulter; end loop; end loop; end;$body$ language 'plpgsql' volatile; 

However, when I actually run the function with

 select * from fn_get_data('', 30) 

I get this error:

 ERROR: column "t_search" does not exist LINE 3: and (t_name ~* t_search or t_description ~* t_search) ^ QUERY: prepare prep_stmt(integer) as select * from store where n_status > 0 and t_tag is not null and n_museum = $1 and (t_name ~* t_search or t_description ~* t_search) order by dt_modified desc limit maxres_free 

Well, maybe he doesn't like external variables in a prepared statement, so I changed this to

 prepare prep_stmt(integer, varchar, integer) as select * from store where n_status > 0 and t_tag is not null and n_museum = $1 and (t_name ~* $2 or t_description ~* $2) order by dt_modified desc limit $3 ... for resulter in execute prep_stmt(mid, t_search, n_max) ... 

This time I get another error:

 ERROR: function prep_stmt(integer, character varying, integer) does not exist LINE 1: SELECT prep_stmt(mid, t_search, n_max) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT prep_stmt(mid, t_search, n_max) 

What am I missing here?

EDIT I added the corresponding table structure at the top.

Answer 1

It seems to me that PL / PgSQL EXECUTE for dynamic SQL trumps regular SQL EXECUTE for prepared statements.

the code:

 create or replace function prep_test() returns void as $$ begin PREPARE do_something AS SELECT 1; EXECUTE do_something; end; $$ LANGUAGE 'plpgsql'; 

Test:

 regress=# select prep_test(1); ERROR: column "do_something" does not exist LINE 1: SELECT do_something ^ QUERY: SELECT do_something CONTEXT: PL/pgSQL function "prep_test" line 4 at EXECUTE statement 

outside of PL / PgSQL, it works fine:

 regress=# EXECUTE do_something; ?column? ---------- 1 (1 row) 

I'm not sure how you executed the prepared statement in PL / PgSQL.

Of interest, why are you trying to use prepared statements in PL / PgSQL? Plans are prepared and cached for PL / PgSQL anyway, this happens automatically.

Answer 2

There is an EXECUTE method for a prepared statement in a function, but like the accepted answer, you usually do not do this in a function because the function already saves its plan.

However, there are still cases where you need to use a prepared statement in a function. My use case is to use multiple schemas for different users, where the schemas contain similar names, and you want to use the same function to access one of these tables based on what is set for the search_path parameter. In this situation, because the function saves its plan, using the same function after changing the search_path break things. I have already said two solutions to this problem. The first is to use EXECUTE '<Your query as a string here>' . But this can become very ugly for large queries, hence the reason for using the second method, which includes PREPARE .

So, given why you want to do this aside, here's how:

 CREATE OR REPLACE FUNCTION prep_test() RETURNS void AS $$ BEGIN PREPARE do_something AS SELECT 1; EXECUTE 'EXECUTE do_something;'; END; $$ LANGUAGE plpgsql; 

Although it is likely to be in your best interest to add some protection measures so that it does not break. Sort of:

 CREATE OR REPLACE FUNCTION prep_test() RETURNS void AS $$ BEGIN IF (SELECT count(*) FROM pg_prepared_statements WHERE name ilike 'do_something') > 0 THEN DEALLOCATE do_something; END IF; PREPARE do_something AS SELECT 1; EXECUTE 'EXECUTE do_something;'; DEALLOCATE do_something; END; $$ LANGUAGE plpgsql; 

Again, those who think that they want to do this usually should not, but for those cases where it is necessary, you do it.

Answer 3

You can use an EXECUTE statement like this in PLPGSQL:

 select magicvalue into str_execute from magicvalues where magickey = ar_requestData[2]; EXECUTE str_execute into str_label USING ar_requestData[3], ar_requestData[4]::boolean, ar_requestData[5]::int, ar_requestData[6]; 

This is the code that I use in my application. ar_requestData is an array with text values. The magicvalues ​​table stores things like prepared statements. The select statement has the form:

 insert into classtypes(label, usenow, ranking, description) values($1,$2,$3,$4) returning label' 

Respectfully,

Loek Bergman

Answer 4

The PREPARE statement is not allowed in plpgsql. After that, you can combine all the operators inside the function and use dynamic start. Here is an example.

 create or replace function sp_test(f_total int) returns void as $ytt$ declare v_sql text; declare i int; begin v_sql:='prepare ytt_s1 (int,timestamp) as select * from tbl1 where id = $1 and log_time = $2;'; while i < f_total loop v_sql:=v_sql||'execute ytt_s1('||i||',now());'; i := i + 1; end loop; v_sql:=v_sql||'deallocate ytt_s1;'; execute v_sql; end; $ytt$ language plpgsql;