pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedimprovise callbacks in plpgsql
10+ messages / 5 participants
[nested] [flat]
* improvise callbacks in plpgsql
@ 2005-11-01 19:00 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 10+ messages in thread
From: Merlin Moncure @ 2005-11-01 19:00 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: improvise callbacks in plpgsql
@ 2005-11-01 21:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-11-01 21:29 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: improvise callbacks in plpgsql
@ 2005-11-01 22:13 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 10+ messages in thread
From: Merlin Moncure @ 2005-11-01 22:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: improvise callbacks in plpgsql
@ 2005-11-01 23:04 Jim C. Nasby <jnasby@pervasive.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jim C. Nasby @ 2005-11-01 23:04 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: improvise callbacks in plpgsql
@ 2005-11-02 13:45 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 10+ messages in thread
From: Merlin Moncure @ 2005-11-02 13:45 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: improvise callbacks in plpgsql
@ 2005-11-02 16:47 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2005-11-02 16:47 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: improvise callbacks in plpgsql
@ 2005-11-02 17:04 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2005-11-02 17:04 UTC (permalink / raw)
To: pgsql-performance
oops. my blog is here: :-)
http://people.planetpostgresql.org/merlin/
>
http://www.postgresql.org/docs/8.0/interactive/plpgsql.html#PLPGSQL-ADVA
> NTAGES
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] improvise callbacks in plpgsql
@ 2005-11-02 23:44 Jim C. Nasby <jnasby@pervasive.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jim C. Nasby @ 2005-11-02 23:44 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-www@postgresql.org
Can we get a link to this posted somewhere? I guess on techdocs?
On Wed, Nov 02, 2005 at 08:45:02AM -0500, Merlin Moncure wrote:
> > 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
>
--
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] improvise callbacks in plpgsql
@ 2005-11-03 02:52 David Fetter <david@fetter.org>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 10+ messages in thread
From: David Fetter @ 2005-11-03 02:52 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Merlin Moncure <merlin.moncure@rcsonline.com>; pgsql-www@postgresql.org
On Wed, Nov 02, 2005 at 05:44:30PM -0600, Jim C. Nasby wrote:
> Can we get a link to this posted somewhere? I guess on techdocs?
>
> On Wed, Nov 02, 2005 at 08:45:02AM -0500, Merlin Moncure wrote:
> > > 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).
This might be worth putting in the docs somewhere. Tutorial?
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [PERFORM] improvise callbacks in plpgsql
@ 2005-11-03 14:01 Merlin Moncure <mmoncure@gmail.com>
parent: David Fetter <david@fetter.org>
0 siblings, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2005-11-03 14:01 UTC (permalink / raw)
To: David Fetter <david@fetter.org>; +Cc: pgsql-www@postgresql.org; jnasby@pervasive.com
On 11/2/05, David Fetter <david@fetter.org> wrote:
> On Wed, Nov 02, 2005 at 05:44:30PM -0600, Jim C. Nasby wrote:
> > Can we get a link to this posted somewhere? I guess on techdocs?
> >
> > On Wed, Nov 02, 2005 at 08:45:02AM -0500, Merlin Moncure wrote:
> > > > 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).
>
> This might be worth putting in the docs somewhere. Tutorial?
you guys can do anything you like with it...
I'm working on part two which will build on the previous example and
show how to pass in a function to use as a callback, kind of like a
functor.
btw, the blog examples are a reduction of my own personal code which
went through a vast simplification process. I need to test it a bt
before it hits doc quality, there might be some errors lurking there.
Merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-11-03 14:01 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-01 19:00 improvise callbacks in plpgsql Merlin Moncure <merlin.moncure@rcsonline.com>
2005-11-01 21:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-01 22:13 Re: improvise callbacks in plpgsql Merlin Moncure <merlin.moncure@rcsonline.com>
2005-11-01 23:04 ` Jim C. Nasby <jnasby@pervasive.com>
2005-11-02 13:45 Re: improvise callbacks in plpgsql Merlin Moncure <merlin.moncure@rcsonline.com>
2005-11-02 23:44 ` Jim C. Nasby <jnasby@pervasive.com>
2005-11-03 02:52 ` David Fetter <david@fetter.org>
2005-11-03 14:01 ` Merlin Moncure <mmoncure@gmail.com>
2005-11-02 16:47 Re: improvise callbacks in plpgsql Merlin Moncure <merlin.moncure@rcsonline.com>
2005-11-02 17:04 Re: improvise callbacks in plpgsql Merlin Moncure <merlin.moncure@rcsonline.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox