Thread: Any tutorial or FAQ on building an extension?

Hello Group,

I'd like to build an extension to PostgreSQL.  It will intercept
queries and perform some transformations on the query and on the data
returned, given some business rules that the users have specified.

What's the best way to do this?  It seems like if I model the pgpool-
II architecture that would be a good start. That way existing clients
don't really know that they are talking to my application instead of a
real PostgreSQL postmaster.  My app won't be written in C but I
suppose I could start with this approach.

Is there an easier way of going about this other than replacing the
postmaster / postgres components?

Thanks for any pointers,

Matt

> Is there an easier way of going about this other than replacing the
> postmaster / postgres components?

I'd start with creating my own extended version to psql (the client
library), I suppose.  But since I don't really know what kind of
"transformations" you have in mind, any advice is going to be purely
speculative.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Aug 11, 1:11 pm, j...@agliodbs.com (Josh Berkus) wrote:
> > Is there an easier way of going about this other than replacing the
> > postmaster / postgres components?
>
> I'd start with creating my own extended version to psql (the client
> library), I suppose.  But since I don't really know what kind of
> "transformations" you have in mind, any advice is going to be purely
> speculative.
>

Thanks for the response Josh.

I'm not sure that psql is the right thing for me to do though, since I
want to build a back-end component that takes the place of the
existing postmaster.  Very possible I misunderstood you though.

To clarify, essentially what I want to do is this:

Client [ psql | JDBC driver | pgAdmin | etc. ] issues a Query
[ "Select * from sales" ]   |   |   \/
My new component intercepts this, and decides if it wants to do
something   |   |   \/
If it does not, it simply passes this on to the real PostgreSQL server
running somewhere   |   |   \/
If it does, it passes the request over to my new server (via sockets),
does its work, and pass back the results   |   |   \/
The client gets the results back, either from PostgreSQL or from my
new server, and goes about its way.

Matt Culbreth <mattculbreth@gmail.com> wrote: 
> My new component intercepts this, and decides if it wants to do
> something
> If it does, it passes the request over to my new server (via
> sockets), does its work, and pass back the results
That's still too vague to allow people to give very specific advice. 
For example, I don't have a clue yet whether the query rewrite rules
might satisfy your needs:
http://www.postgresql.org/docs/8.4/interactive/sql-createrule.html
-Kevin

On Tue, Aug 11, 2009 at 4:00 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:
> Matt Culbreth <mattculbreth@gmail.com> wrote:
>
>> My new component intercepts this, and decides if it wants to do
>> something
>
>> If it does, it passes the request over to my new server (via
>> sockets), does its work, and pass back the results
>
> That's still too vague to allow people to give very specific advice.
> For example, I don't have a clue yet whether the query rewrite rules
> might satisfy your needs:
>
> http://www.postgresql.org/docs/8.4/interactive/sql-createrule.html

Yeah.  I suspect you are going to better off using some combination of
views, triggers, set-returning functions, and SQL-level permissions to
do whatever it is that you are trying to do here rather than inventing
a whole middleware layer.

...Robert