Re: [PERFORM] improvise callbacks in plpgsql
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
Re: [PERFORM] improvise callbacks in plpgsql
> 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
> 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
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
Re: [PERFORM] improvise callbacks in plpgsql
> 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
Re: [PERFORM] improvise callbacks in plpgsql
"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
[PERFORM] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend