Skip site navigation (1) Skip section navigation (2)

Using EXECUTE in a function

From: Andreas Tille <tillea(at)rki(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Using EXECUTE in a function
Date: 2007-07-31 14:10:03
Message-ID: Pine.LNX.4.64.0707311529490.15955@wr-linux02 (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-performance by date

Next:From: Camilo PortoDate: 2007-07-31 14:35:56
Subject: Semantics of PostgreSQL Server Log Stats
Previous:From: Henrik ZagerholmDate: 2007-07-31 08:23:38
Subject: Seq scan on join table despite index and high statistics

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group