pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing EXECUTE in a function
2+ messages / 2 participants
[nested] [flat]
* Using EXECUTE in a function
@ 2007-07-31 14:10 Andreas Tille <tillea@rki.de>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas Tille @ 2007-07-31 14:10 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have found under
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).
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.
Kind regards and thanks for any help
Andreas.
--
http://fam-tille.de
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using EXECUTE in a function
@ 2007-08-01 03:28 Merlin Moncure <mmoncure@gmail.com>
parent: Andreas Tille <tillea@rki.de>
0 siblings, 0 replies; 2+ messages in thread
From: Merlin Moncure @ 2007-08-01 03:28 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: pgsql-performance
On 7/31/07, Andreas Tille <tillea@rki.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-08-01 03:28 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-31 14:10 Using EXECUTE in a function Andreas Tille <tillea@rki.de>
2007-08-01 03:28 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox