Using a prepared statement in a stored function in PostgreSQL

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 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_ma…

Accepted Answer

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.

Using a prepared statement in a stored function in PostgreSQL

Solution

Problem