Re: Using EXECUTE in a function

On 7/31/07, Andreas Tille <tillea(at)rki(dot)de> wrote:
http://www.physiol.ox.ac.uk/Computing/Online_Documentation/postgresql/plpgsql.html#PLPGSQL-OVERVIEW
>
> Note: The PL/pgSQL EXECUTE statement is not related to the EXECUTE
> statement supported by the PostgreSQL server. The server's EXECUTE
> statement cannot be used within PL/pgSQL functions (and is not needed).

If I read the documentation correctly, EXECUTE is not needed because
query plans are generally cached within pl/pgsql after the first
execution of the function.

> I'm especially stumbling over the "is not needed" part. My plan
> is to write a server side function (either SQL or pgsql) that wraps
> the output of a PREPAREd statement but I have no idea how to do this.
>
> The final task is to obtain some XML for of my data via a simple shell script
> that contains
>
> psql -t MyDatabase -c 'SELECT * FROM MyFunction ($1, $2);'
>
> The task of MyFunction($1,$2) is to wrap up the main data into an XML
> header (just some text like
> <?xml version="1.0" encoding="ISO-8859-1"?>
> ...
> ) around the real data that will be obtained via a PREPAREd statement that is
> declared like this
>
> PREPARE xml_data(int, int) AS ( SELECT ... WHERE id = $1 AND source = $2 );
>
> where "..." stands for wrapping the output into xml format.
>
> I don't know whether this is a reasonable way. I know how to solve this
> problem when using a pgsql function and preparing the output as a text
> string but I learned that PREPAREd statements might be much more clever
> performance wise and thus I wonder whether I could do it this way.

prepared statements are the fastest possible way to execute queries
but generally that extra speed is not measurable or only useful under
specific conditions. also, prepared statements can't be folded into
queries the way functions can:

select xml_data(foo, bar) from baz;

so, I'd stick with the function approach (I think that's what you are
asking). If you are doing XML work you may be interested in the
upcoming xml features of 8.3:

http://developer.postgresql.org/pgdocs/postgres/functions-xml.html

merlin