Re: [PERFORM] improvise callbacks in plpgsql

2005-11-02 Thread Merlin Moncure
 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




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PERFORM] improvise callbacks in plpgsql

2005-11-02 Thread Merlin Moncure
 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] improvise callbacks in plpgsql

2005-11-02 Thread Merlin Moncure
oops. my blog is here: :-)
http://people.planetpostgresql.org/merlin/


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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


[PERFORM] improvise callbacks in plpgsql

2005-11-01 Thread Merlin Moncure
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

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PERFORM] improvise callbacks in plpgsql

2005-11-01 Thread Tom Lane
Merlin Moncure [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] improvise callbacks in plpgsql

2005-11-01 Thread Jim C. Nasby
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  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq