Thread: improvise callbacks in plpgsql

hello performance minded administrators:

We have recently converted a number of routines that walk a bill of
materials (which is a nested structure) from the application side to the
server side via recursive plpgsql functions.  The performance is
absolutely fantastic but I have to maintain a specialized 'walker' for
each specific task that I have to do.  It would be very nice and elegant
if I could pass in the function for the walker to execute while it is
iterating through the bill of materials.  I have been beating my head
against the wall for the best way to do this so here I am shopping for
ideas.

A simplified idealized version of what I would like to do is
    begin
          select (callback_routine)(record_type)
    end;

from within a plpgsql function.  I am borrowing the C syntax for a
function pointer here.  The problem I am running into is the only way to
do callbacks is via dynamic sql...however you can use higher level types
such as row/record type in dynamic sql (at least not efficiently).  I
could of course make a full dynamic sql call by expanding the record
type into a large parameter list but this is unwieldy and brittle.

Any thoughts?

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> A simplified idealized version of what I would like to do is
>     begin
>           select (callback_routine)(record_type)
>     end;
> from within a plpgsql function.  I am borrowing the C syntax for a
> function pointer here.

Well, there's no function pointer type in SQL :-(.  I don't see any way
to do what you want in pure plpgsql.  If you're willing to implement an
auxiliary C function you could probably make it go:

    create function callit(oid, record) returns void ...

where the OID has to be the OID of a function taking a record-type
argument.  The regprocedure pseudotype would allow you not to need
to write any numeric OIDs in your code:

    select callit('myfunc(record)'::regprocedure, recordvar);

The body of callit() need be little more than OidFunctionCall1()
plus whatever error checking and security checking you want to
include.

            regards, tom lane

> The body of callit() need be little more than OidFunctionCall1()
> plus whatever error checking and security checking you want to
> include.

esp=# create table test(f text);
CREATE TABLE

esp=# create function test() returns void as
$$
    begin
        insert into test values ('called');
    end;
$$ language plpgsql;

esp=# create or replace function test2() returns void as
esp-# $$
esp$#     declare
esp$#         r record;
esp$#     begin
esp$#         select into r 'abc';
esp$#         perform callit('test()'::regprocedure, r);
esp$#     end;
esp$#
esp$# $$ language plpgsql;
CREATE FUNCTION

esp=# select test2();

esp=# select * from test;
   f
--------
 called
(1 row)

one word...
w00t

Merlin

Would you be willing to write up an example of this? We often get asked
about support for WITH, so I bet there's other people who would be very
interested in what you've got.

On Tue, Nov 01, 2005 at 05:13:48PM -0500, Merlin Moncure wrote:
> > The body of callit() need be little more than OidFunctionCall1()
> > plus whatever error checking and security checking you want to
> > include.
>
> esp=# create table test(f text);
> CREATE TABLE
>
> esp=# create function test() returns void as
> $$
>     begin
>         insert into test values ('called');
>     end;
> $$ language plpgsql;
>
> esp=# create or replace function test2() returns void as
> esp-# $$
> esp$#     declare
> esp$#         r record;
> esp$#     begin
> esp$#         select into r 'abc';
> esp$#         perform callit('test()'::regprocedure, r);
> esp$#     end;
> esp$#
> esp$# $$ language plpgsql;
> CREATE FUNCTION
>
> esp=# select test2();
>
> esp=# select * from test;
>    f
> --------
>  called
> (1 row)
>
> one word...
> w00t
>
> Merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

> Would you be willing to write up an example of this? We often get
asked
> about support for WITH, so I bet there's other people who would be
very
> interested in what you've got.

Sure. In fact, I had already decided this to be the next topic on my
blog.  I'm assuming you are asking about tools to deal with recursive
sets in postgresql.  A plpgsql solution is extremely fast, tight, and
easy if you do it right...Tom's latest suggestions (I have to flesh this
out some more) provide the missing piece puzzle to make it really tight
from a classic programming perspective.  I don't miss the recursive
query syntax at all...IMO it's pretty much a hack anyways (to SQL).

Merlin

> Would you be willing to write up an example of this? We often get
asked
> about support for WITH, so I bet there's other people who would be
very
> interested in what you've got.
>
You can see my blog on the subject here:
http://www.postgresql.org/docs/8.0/interactive/plpgsql.html#PLPGSQL-ADVA
NTAGES


It doesn't touch the callback issue.  I'm going to hit that at a later
date, a review would be helpful!

Merlin

oops. my blog is here: :-)
http://people.planetpostgresql.org/merlin/

>
http://www.postgresql.org/docs/8.0/interactive/plpgsql.html#PLPGSQL-ADVA
> NTAGES