C trigger using system(3) to execute a php script
Hi there,
I have written a trigger in C for a postgresql engine (8.0.1) running
on a FreeBSD 5.4-RELEASE i386 machine.
I mainly used the example code from the documentation. The trigger
is working perfectly and it is triggered when I want to.
Then, at some point, I wanted to call an external php script from
the trigger to perform some sql statements against the database.
Before trying that I started writing a very simple php script
which basically created a file in /tmp. It worked.
Then, I wanted to run the full php script from the trigger. I tried
the script manually, form the shell, and it worked perfectly. Notice
that the script doesn't generate any stdin/stdout output. So I changed
the system(3) parameter to call my php script instead of the test one.
I tried it and it didn't work. The php script doesn't perform the
modifications it suppose to in the database.
The php script is about 150 lines long and performs like 5 or 6 sql
queries/inserts against the database.
Can anyone advice me with these?
Thanks in advance,
David
David Rio Deiros <driodeiros@gmail.com> writes:
Then, I wanted to run the full php script from the trigger. I tried
the script manually, form the shell, and it worked perfectly. Notice
that the script doesn't generate any stdin/stdout output. So I changed
the system(3) parameter to call my php script instead of the test one.
I tried it and it didn't work. The php script doesn't perform the
modifications it suppose to in the database.The php script is about 150 lines long and performs like 5 or 6 sql
queries/inserts against the database.
Why would you do it that way? The queries/inserts won't be rolled
back if the transaction containing the trigger aborts (because they'd
be done in a separate session). Why not just do your queries from C
using the SPI interface?
-Doug
On Thu, Feb 16, 2006 at 02:24:06PM -0500, Douglas McNaught wrote:
David Rio Deiros <driodeiros@gmail.com> writes:
Then, I wanted to run the full php script from the trigger. I tried
the script manually, form the shell, and it worked perfectly. Notice
that the script doesn't generate any stdin/stdout output. So I changed
the system(3) parameter to call my php script instead of the test one.
I tried it and it didn't work. The php script doesn't perform the
modifications it suppose to in the database.The php script is about 150 lines long and performs like 5 or 6 sql
queries/inserts against the database.Why would you do it that way? The queries/inserts won't be rolled
back if the transaction containing the trigger aborts (because they'd
be done in a separate session). Why not just do your queries from C
using the SPI interface?
The code was already in php... plus I am including some other php
files so I would have to parse that files from C if I write all in C. I
basically didn't want to do extra work.
Thanks for the answer,
David
David Rio Deiros <driodeiros@gmail.com> writes:
The code was already in php... plus I am including some other php
files so I would have to parse that files from C if I write all in C. I
basically didn't want to do extra work.
There's a PL/PHP out there, but I don't know if it's sufficiently
advanced to be able to write triggers in it...
-Doug
Douglas McNaught wrote:
David Rio Deiros <driodeiros@gmail.com> writes:
The code was already in php... plus I am including some other php
files so I would have to parse that files from C if I write all in C. I
basically didn't want to do extra work.There's a PL/PHP out there, but I don't know if it's sufficiently
advanced to be able to write triggers in it...
Yes quite... you can write triggers, srf functions, polymorphic types,
all kinds of stuff.
Review:
http://projects.commandprompt.com/projects/public/plphp
Sincerely,
Joshua D. Drake
-Doug
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Hi there,
I just wanted to let you know that I finally made the trigger work using
an external php script. Actually it always worked, the problem is that
I was trying the trigger in one machine and testing the results in other.
I know, very lame.
Some people suggested plphp, I tried it but I had issues trying to compile
it. But the project looks promising anyway.
Also, I took me a while to figure out how to compile my trigger on
FreeBSD and MacOSX/Darwin. Finally I found this in the documentation:
http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html
which perfectly explains what flags to pass to gcc in order to
compile your code on those and other platforms.
I didn't find any link or reference to that document in the trigger
section. Do you think it would be a good idea to do that?
Thanks,
David
Show quoted text
David Rio Deiros <driodeiros@gmail.com> writes:
Then, I wanted to run the full php script from the trigger. I tried
the script manually, form the shell, and it worked perfectly. Notice
that the script doesn't generate any stdin/stdout output. So I changed
the system(3) parameter to call my php script instead of the test one.
I tried it and it didn't work. The php script doesn't perform the
modifications it suppose to in the database.The php script is about 150 lines long and performs like 5 or 6 sql
queries/inserts against the database.
On Sat, Feb 18, 2006 at 10:59:16AM -0800, David Rio Deiros wrote:
Also, I took me a while to figure out how to compile my trigger on
FreeBSD and MacOSX/Darwin. Finally I found this in the documentation:http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html
which perfectly explains what flags to pass to gcc in order to
compile your code on those and other platforms.
Take a look at "Extension Building Infrastructure" on that page and
also at the contrib modules' Makefiles. In most cases you can let
PostgreSQL figure out how to build your code.
http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#XFUNC-C-PGXS
--
Michael Fuhr
On Sat, Feb 18, 2006 at 12:34:23PM -0700, Michael Fuhr wrote:
On Sat, Feb 18, 2006 at 10:59:16AM -0800, David Rio Deiros wrote:
Also, I took me a while to figure out how to compile my trigger on
FreeBSD and MacOSX/Darwin. Finally I found this in the documentation:http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html
which perfectly explains what flags to pass to gcc in order to
compile your code on those and other platforms.Take a look at "Extension Building Infrastructure" on that page and
also at the contrib modules' Makefiles. In most cases you can let
PostgreSQL figure out how to build your code.http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#XFUNC-C-PGXS
Thanks Michael. I am going to rewrite my Makefile using PGXS.