Call Web Service(asmx) from PostgreSql function every X hours

Question

I have got a function in PostgreSql:

do $$
  declare
    arow record;
    foo varchar(50);
  begin
    for arow in
      select name from person s(a)
    loop
      foo := arow.name;
      RAISE NOTICE 'Calling asmx(%)', foo;
    end loop;
  end;
$$;

I would like to call a Web Service made in C#(asmx), in the line where I print the NOTICE.

Is it possible? If not, any suggestions?

Most cases calling a web service from a database function is on the path to performance problems, but I'll take it as is. Using a language like plperl or plpython you can call anything. Go figure out how you would call your webservice from the command line and using one of perl or pythons libraries to make a system call using thhe web service call only you can figure out given the info in the question. If you really want to go down this path or if your just seeing if it's possible read up on untrusted plperl(http://www.postgresql.org/docs/9.3/static/plperl-trusted.html) — Kuberchaun, Jul 23 '14 at 14:59

score 2 · Accepted Answer · edited May 23 '17 at 11:57

While it's possible to do this from a long running function it's a terrible idea for many reasons.

Instead, you should NOTIFY on a channel, then have a LISTENing client daemon receive the notifications and call the web service.

This is essentially the same problem as "how do I send an email from a function/trigger", with all the same problems, like:

It wastes a perfectly good DB connection - while the web service call is happening that DB process can do nothing useful
If the web service call gets stuck you can land up with a lot of stuck DB connections causing serious performance issues, connection pool starvation, etc

If you do decide you must call the web service directly from the function you will need to use PL/Perl, PL/python, PL/TCL, PL/Java, or implement a C function to make the call. You can't do it in PL/PgSQL directly as it has no TCP/IP, HTTP, etc support (by design; it's meant to be contained to in-database operations and limited by the database's security model). Your Pl/PgSQL function can call one in another language. Calling the web service from that language is just like calling it from any other function in that language (except you can't use threads).

Please don't, though. Use LISTEN and NOTIFY. That's what they're there for.

Re the "every few hours" bit: Don't try to do that with a DB function. It'll cause performance issues due to non-removable row versions caused by the function's long running transaction. Use task scheduler / cron / pgagent / ...

Call Web Service(asmx) from PostgreSql function every X hours

1 Answers1