From: Andreas Tille <tillea@rki.de>
To: pgsql-performance@postgresql.org
Subject: Using EXECUTE in a function
Date: Tue, 31 Jul 2007 16:10:03 +0200 (CEST)
Message-ID: <Pine.LNX.4.64.0707311529490.15955@wr-linux02> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tillea@rki.de
Subject: Re: Using EXECUTE in a function
In-Reply-To: <Pine.LNX.4.64.0707311529490.15955@wr-linux02>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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