Thread: more docs on extending postgres in C
I haven't been able to find anything better than the online manual and pg source code to learn how to write extensions. I couldn't find a reference of all the function/macros I could use and some more examples on how to use them. I'd also appreciate some suggestion about dev environment and best practices on Debian, something that could help me to compile, install, test easily on Debian. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
2010/1/20 Ivan Sergio Borgonovo <mail@webthatworks.it>: > I haven't been able to find anything better than the online manual > and pg source code to learn how to write extensions. > > I couldn't find a reference of all the function/macros I could use > and some more examples on how to use them. > > I'd also appreciate some suggestion about dev environment and best > practices on Debian, something that could help me to compile, > install, test easily on Debian. Try this: www.joeconway.com/tut_oscon_2004.pdf -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
Hello I wrote one article some years ago http://www.postgres.cz/index.php/Project_of_UDF_and_its_realization_at_C_for_PostgreSQL The core is correct, but there are some issues, it is for 8.0. regards Pavel Stehule 2010/1/20 Ivan Sergio Borgonovo <mail@webthatworks.it>: > I haven't been able to find anything better than the online manual > and pg source code to learn how to write extensions. > > I couldn't find a reference of all the function/macros I could use > and some more examples on how to use them. > > I'd also appreciate some suggestion about dev environment and best > practices on Debian, something that could help me to compile, > install, test easily on Debian. > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2010/1/20 Ivan Sergio Borgonovo <mail@webthatworks.it>: > I haven't been able to find anything better than the online manual > and pg source code to learn how to write extensions. > > I couldn't find a reference of all the function/macros I could use > and some more examples on how to use them. look on contrib directory in pg source code. Every subdir is good example. Pavel > > I'd also appreciate some suggestion about dev environment and best > practices on Debian, something that could help me to compile, > install, test easily on Debian. > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > I haven't been able to find anything better than the online manual > and pg source code to learn how to write extensions. Maybe this will help: http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf http://github.com/dimitri/prefix Regards, -- dim
On Wed, 20 Jan 2010 16:56:04 +0100 Dimitri Fontaine <dfontaine@hi-media.com> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > I haven't been able to find anything better than the online > > manual and pg source code to learn how to write extensions. > > Maybe this will help: > http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf > http://github.com/dimitri/prefix Thanks to all. Is there a reference of all macro and functions? -- Ivan Sergio Borgonovo http://www.webthatworks.it
2010/1/20 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Wed, 20 Jan 2010 16:56:04 +0100 > Dimitri Fontaine <dfontaine@hi-media.com> wrote: > >> Ivan Sergio Borgonovo <mail@webthatworks.it> writes: >> > I haven't been able to find anything better than the online >> > manual and pg source code to learn how to write extensions. >> >> Maybe this will help: >> http://wiki.postgresql.org/wiki/Image:Prato_2008_prefix.pdf >> http://github.com/dimitri/prefix > > Thanks to all. > > Is there a reference of all macro and functions? > no, only source code Pavel > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Wed, 20 Jan 2010 17:38:17 +0100 Pavel Stehule <pavel.stehule@gmail.com> wrote: > > Is there a reference of all macro and functions? > no, only source code It would be nice to at least a list of functions that could be used in extension development to avoid reading all the source. Since I'm a new entry in pg C coding and internals it will take me ages to just find what's worth to know. I'll try to take some notes while I grasp stuff and publish them somewhere. I'm still trying to digest: There are two ways you can build a composite data value (henceforth a "tuple"): you can build it from an array of Datum values, or from an array of C strings that can be passed to the input conversion functions of the tuple's column data types. In either case, you first need to obtain or construct a TupleDesc descriptor for the tuple structure. When working with Datums, you pass the TupleDesc to BlessTupleDesc, and then call heap_form_tuple for each row. When working with C strings, you pass the TupleDesc to TupleDescGetAttInMetadata, and then call BuildTupleFromCStrings for each row. In the case of a function returning a set of tuples, the setup steps can all be done once during the first call of the function. I grep throu contrib and I wasn't able to find anything that really enlighted me about BlessTupleDesc. I'll try to see if tomorrow things will look clearer. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo wrote:
> It would be nice to at least a list of functions that could be used
> in extension development to avoid reading all the source.
>
psql -E
\df *
This will dump out a list of all the built-in functions in the server.
It will also show you the query that did so, I get one that looks like this:
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN
'trigger'
ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
If you run that, and maybe filter it down to only look at stuff similar
to what you're looking for (example: only show things where the Result
data type returns 'text' because that's what you need), that will give
you an idea what functions might be suitable to borrow from. grep for
them in the source code, you'll find them only once in the table that
maps them to actual function names on the code. Grab that name, grep
for it, and then you'll be sitting at example code that might be
interesting to you.
The other thing I'd recommend is surfing the code via
http://doxygen.postgresql.org/ , along with reading through the modules
in contrib/ as already suggested (which won't be found by the query
above because they're optional).
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
On Wednesday 20 January 2010 15.42:14 Ivan Sergio Borgonovo wrote: > I'd also appreciate some suggestion about dev environment and best > practices on Debian, something that could help me to compile, > install, test easily on Debian. (Disclaimer: Haven't done any postgres related programming so far, so this might need adjusting.) The desciption for package postgresql-server-dev-8.4 includes [[[ This package also contains the Makefiles necessary for building add-on modules of PostgreSQL, which would otherwise have to be built in the PostgreSQL source-code tree. ]]] so this package plus C compiler and the other usual tools should get you started. (There is also the 'build-essential' Debian package which will ensure you have a basic C/C++ development environment. For other packages you might need to build extensions you could look at the build dependencies of the postgresql-8.4 *source* Debian package; I'm offline right now but I think <http://packages.qa.debian.org/postgresql-8.4> should get you this information.) If you find that you can not compile an extension with only this server-dev package but need other stuff from the PostgreSQL source, I'm sure the PostgreSQL packagers would appreciate a bug report.) (I hope I'm not just telling you stuff you alredy know; sorry if so.) cheers -- vbi -- Today is Setting Orange, the 20th day of Chaos in the YOLD 3176
Attachment
On Wed, 20 Jan 2010 17:43:27 +0100 Adrian von Bidder <avbidder@fortytwo.ch> wrote: > On Wednesday 20 January 2010 15.42:14 Ivan Sergio Borgonovo wrote: > > I'd also appreciate some suggestion about dev environment and > > best practices on Debian, something that could help me to > > compile, install, test easily on Debian. > > (Disclaimer: Haven't done any postgres related programming so far, > so this might need adjusting.) > > The desciption for package postgresql-server-dev-8.4 includes > > [[[ > This package also contains the Makefiles necessary for building > add-on modules of PostgreSQL, which would otherwise have to be > built in the PostgreSQL source-code tree. > ]]] I need some babysitting from someone that actually developed extension on Debian. The package was already installed, but it seems it provides more than just the header files. Now that I know I can build extensions outside pg source tree I'd be nice I understand how to make it in a kosher way. If I had to build stuff in the pg source tree I'd just clone a contrib directory and change the makefile [1]. What am I supposed to do if I'd like to create a contrib elsewhere (eg. ~/Documents/nfs/projects/ts_extensions)? I've seen some forward to this list from pgsql-hackers. Would that list be a better place to ask this kind of things or is it too elite ;) for newbies? [1] actually I tried to make in a contrib module dir in pg source code tree... but unless you run ./configure at the root of the source tree it fails. So I've to guess that -dev gives you a preconfigured environment. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > If I had to build stuff in the pg source tree I'd just clone a > contrib directory and change the makefile [1]. What am I supposed to > do if I'd like to create a contrib elsewhere (eg. > ~/Documents/nfs/projects/ts_extensions)? Use PGXS: http://www.postgresql.org/docs/8.4/static/xfunc-c.html#XFUNC-C-PGXS If the docs aren't enough for you, all of the contrib modules can be built via pgxs, using "make PGXS=1". So their makefiles are useful examples. regards, tom lane
On Fri, 22 Jan 2010 11:02:46 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > If I had to build stuff in the pg source tree I'd just clone a > > contrib directory and change the makefile [1]. What am I > > supposed to do if I'd like to create a contrib elsewhere (eg. > > ~/Documents/nfs/projects/ts_extensions)? > > Use PGXS: > http://www.postgresql.org/docs/8.4/static/xfunc-c.html#XFUNC-C-PGXS > > If the docs aren't enough for you, all of the contrib modules can > be built via pgxs, using "make PGXS=1". So their makefiles are > useful examples. What did work for me on Debian sid was: - installing postgresql-server-dev-[version] - apt-get source postgresql-server-dev-[version] - copy from there a contrib dir in my ~ (or wherever you prefer) - export USE_PGXS=1; make I didn't have postgresql server installed on this box but I have stuff that got in to satisfy dependencies for php/python pg drivers and psql. I wasn't able to understand from where PGXS pick up the version since I installed -dev-8.3 but everything else was for 8.4 and it didn't work: ivan@dawn:~/ts_extension$ export USE_PGXS=1; make Makefile:12: /usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory make: *** No rule to make target `/usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk'. Stop. Installing dev-8.4 made everything work. Nothing that really trouble me... but I'd expect that installing -dev-8.3 it would look in the right place since actually pgxs.mk is included in both versions. Thanks... I'd publish a summary as soon as I've clearer ideas so that the next poor guy will find easier to write contrib on Debian. I don't know if the version problem is worth a bug report to Debian (or pg people that built the pgxs system). -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > - installing postgresql-server-dev-[version] > - apt-get source postgresql-server-dev-[version] > - copy from there a contrib dir in my ~ (or wherever you prefer) > - export USE_PGXS=1; make Don't forget apt-get build-dep postgresql-[version] > I didn't have postgresql server installed on this box but I have > stuff that got in to satisfy dependencies for php/python pg drivers > and psql. > I wasn't able to understand from where PGXS pick up the version > since I installed -dev-8.3 but everything else was for 8.4 and it > didn't work: See pg_config, there's the default one then one per major version. > Thanks... I'd publish a summary as soon as I've clearer ideas so > that the next poor guy will find easier to write contrib on Debian. > > I don't know if the version problem is worth a bug report to Debian > (or pg people that built the pgxs system). I' working on this, see the following email and thread. http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/2010-January/000546.html In short it's about doing a VPATH build and setting some variables, then building a PGXS extension is very easy. Regards, -- dim