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

The code shown declares a function fn_get_data() with 2 arguments but you call it with 1 argument, so the function actually called is not the one you're showing. Try \df fn_get_data in psql. — Daniel Vérité
– Daniel Vérité, Commented Oct 3, 2012 at 11:30
@DanielVérité Hello? Please read the question carefully. My problem is not with calling the function. I am declaring a prepared statement, which, for some reason postgres is not interpreting correctly. — Aleks G
– Aleks G, Commented Oct 3, 2012 at 11:34
Read your error message: the prepare complains about a query that starts with select * from store where n_status > 0 while the code you're showing don't even refer to n_status. Again: you're not executing the code you think you are. — Daniel Vérité
– Daniel Vérité, Commented Oct 3, 2012 at 11:37
@DanielVérité Ok, maybe I wasn't very clear in my question. I updated it with more information. What you are stating is completely irrelevant, especially in light of the updated question. I am executing exactly the code I think I am. I started with a fully properly working stored function and all I am trying to do is to pull out a repeated select and use a prepared statement in its place using that select. — Aleks G
– Aleks G, Commented Oct 3, 2012 at 11:49

Craig Ringer · Accepted Answer · 2012-10-03 12:04:04Z

10

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.

answered Oct 3, 2012 at 12:04

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aleks G Over a year ago

Yeah, that's what it seems is happening. As I stated, I have to execute exactly the same SQL in a loop with just one parameter in a where clause being different. If the plans are cached for the entire stored function before it executes, then, I guess, there's no point in using prepared statement. Thanks.

Aleks G Over a year ago

Aha! Found this note in the postgres documentation: Note: The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL statement supported by the PostgreSQL server. The server's EXECUTE statement cannot be used directly within PL/pgSQL functions (and is not needed).

Trevor Young Over a year ago

I fully agree with the answer that this is something that you typically don't want to do because the function already prepares and caches it's plan anyway, but there are some rare cases where you actually do want to be able to use prepared statements in a function, and this is a way to do so. My answer below shows how (and gives a reason why). stackoverflow.com/a/27995157/2555692

slevin Over a year ago

Are dynamic queries with EXECUTE...format...USING in pl/pgsql automatically prepared too? The way I understand it, in the case of dynamic queries, you have to use EXECUTE...format...USING or you are in risk. Only static queries with fixed values are autmatically prepared. Again, I might be wrong. Still searching...

cstork Over a year ago

@slevin What are the results of your search?

Trevor Young · Accepted Answer · 2015-01-17 01:17:22Z

6

There is a way to EXECUTE a prepared statement in a function, but like the accepted answer said, you typically don't wan't to do this in a function because the function already stores its plan.

That being said, there are still use cases where you do need to use a prepared statement in a function. My use case for this is when using multiple schemas for different users where the schemas contain tables that are similarly named and you want to use the same function to access one of these tables based off of what the search_path is set to. In this situation, because of the way the function stores its plan, using the same function after changing the search_path causes things to break. There are two solutions to this problem that I've stated. The first is to use EXECUTE '<Your query as a string here>'. But this can get very ugly for large queries, hence the reason to use the second method, which involves a PREPARE.

So with the background as to 'why' you'd want to do this out of the way, here is the how:

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

Though it will probably be in your best interests to add some protections to keep it from breaking. Something like:

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 probably shouldn't, but for those cases where it is needed, this is how you do it.

answered Jan 17, 2015 at 1:17

Trevor Young

5555 silver badges13 bronze badges

2 Comments

Juan Carlos Oropeza Over a year ago

This is the IT version of dont try this at home, we are professionals.

Alexi Theodore Over a year ago

I was going to say just this; I have exactly this scenario at hand. At this point its 100% syntactic sugar, but that counts in my book. The alternative would be to put the entire statement in a text block with placeholders and then EXECUTE it - but I don't like having code in text blocks unless I really need to.

Loek Bergman · Accepted Answer · 2012-10-04 09:26:30Z

2

You could 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 code I use in my application. ar_requestData is an array with text values. In the table magicvalues do I store things like prepared statements. The select statement is for example:

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

With kind regards,

Loek Bergman

answered Oct 4, 2012 at 9:26

Loek Bergman

2,19520 silver badges18 bronze badges

3 Comments

Aleks G Over a year ago

This will be executing a dynamic SQL, effectively re-building the plans on every iteration of the loop, which will be much slower than just executing the SQL as is. This will completely kill off the whole idea of a prepared statement. I think you missed the point in my question.

Loek Bergman Over a year ago

A function in postgresql is like a prepared statement. Postgresql prepares statements automatically. You can help the planner setting a function to stable or immutable, so it can optimize the execution.

Loek Bergman Over a year ago

@AleksG A function in postgresql is itself like a prepared statement. Postgresql prepares statements automatically. You can help the planner setting a function to stable or immutable, so it can optimize the execution. Please take a note at archives.postgresql.org/pgsql-sql/2006-06/msg00159.php. Furthermore wanted I to show you the syntax of 'using ...', because that is helpful for your loop.

Elliott Frisch · Accepted Answer · 2014-01-13 03:35:31Z

1

PREPARE statement is not allowed within plpgsql. You can splice all the statements inside a function and use dynamic execute after then. 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;

edited Jan 13, 2014 at 3:35

Elliott Frisch

202k20 gold badges166 silver badges265 bronze badges

answered Jan 13, 2014 at 3:13

Moon_of_father

3131 gold badge5 silver badges10 bronze badges

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2024-01-30 16:45:51Z

I created person table, then inserted 2 rows into it as shown below:

CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32);

Then, I created the prepared statement my_pre() with a PREPARE statement which can update age with id in person table dynamically as shown below:

PREPARE my_pre(INT, INT) AS
  UPDATE person SET age = $1 WHERE id = $2;

Then, I created and called these my_func() functions which tries to run my_pre() with an EXECUTE statement as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE my_pre(45, 2); -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func();

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE my_pre(my_age, my_id); -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func(45, 2);

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE my_pre($1, $2) USING my_age, my_id; -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func(45, 2);

But I got the same error below:

ERROR: function my_pre(integer, integer) does not exist

In addition, I created and called my_func() function which tries to run my_pre() with an EXECUTE statement as shown below:

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'EXECUTE my_pre($1, $2)' USING my_age, my_id; -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func(45, 2);

But I got the error below:

ERROR: there is no parameter $1

However finally, I could create my_func() function which successfully runs my_pre() with an EXECUTE statement as shown below:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE 'EXECUTE my_pre(45, 2)'; -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func();

And without my_pre(), directly running 'UPDATE ...' with an EXECUTE statement as shown below also worked. *UPDATE ... without '' gets syntax error:

CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = 45 WHERE id = 2'; -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func();

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING my_age, my_id; -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func(45, 2);

CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
BEGIN
  EXECUTE 'UPDATE person SET age = $1 WHERE id = $2' USING $1, $2; -- Here
END;
$$ LANGUAGE plpgsql;

SELECT my_func(45, 2);

In addition, without my_pre() and a function or procedure, directly running 'UPDATE ...' or UPDATE ... with an EXECUTE statement gets syntax error:

EXECUTE 'UPDATE person SET age = 45 WHERE id = 2';

EXECUTE UPDATE person SET age = 45 WHERE id = 2;

And, I found the syntax of the EXECUTE statement for a PL/pgSQL function or procedure(LANGUAGE plpgsql) as shown below. *An EXECUTE statement cannot be used in a SQL function or procedure(LANGUAGE SQL) otherwise there is error:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

And, I found the syntax of the EXECUTE statement with a prepared statement and without a PL/pgSQL function or procedure as shown below:

EXECUTE name [ ( parameter [, ...] ) ]

Collectives™ on Stack Overflow

Using a prepared statement in a stored function in PostgreSQL

5 Answers 5

5 Comments

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related