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 stored function I need to execute the same select
against this table multiple times:
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 into the stored function. I thought it would make sense to use a prepared statement for this, but I'm running into strange problems. Here's 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 receive 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
Ok, maybe it doesn't like external variables in the prepared statement, so I changed this to be
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 a different 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 relevant table structure at the top.
Looks to me like the PL/PgSQL EXECUTE
for dynamic SQL trumps the regular SQL EXECUTE
for prepared statements.
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 PL/PgSQL it works fine:
regress=# EXECUTE do_something;
?column?
----------
1
(1 row)
I'm not sure how you'd execute a prepared statement within PL/PgSQL.
Out of interest, why are you trying to use prepared statements within PL/PgSQL? Plans are prepared and cached for PL/PgSQL anyway, it happens automatically.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With