PostgreSQL Discussion - ODed on overloads

Author

ODed on overloads

karly@kipshouse.org

2006-03-29, 8:26 pm

I just wanted to get a sanity check on using overloading in
PL/pgSQL.

MY application sends XML requests to a perl script, which has to
parse them and turn them into queries which get sent off, then the
results are put back into XML, and sent back to the client.

The XML language is pretty simple, mostly an element name can map
directly to a stored procedure, and the attributes are arguments to
the procedure. ON many of the queries though, not all of the
attributes are present in every case.

IT seems like a perfect case for using overloading. So far I've
done this by creating the simplest case (fewest arguments) first,
then once that seems to be working, I copy the entire text of the
function, add an argument, and add the code to make that argument
work.

So now I might have 5-10 copies of some of these functions, with a
lot of the code duplicated, which of course creates maintenance
issues.

So if this were Perl, or C, I wouldn't do it this way, but would
have the duplicate code in one function which the other functions
could call.

My question is, is there any penalty for doing this in PL/SQL?
Expecially in functions that return sets. So, if I start with

CREATE FUNCTION getlist(INT)
RETURNS SETOF record AS $$

FOR rec IN SELECT ...

LOOP
RETURN NEXT rec;
END LOOP;

Then if I want to add an argument to return fewer rows I would do

CREATE FUNCTION getlist(INT, INT)
RETURNS SETOF record AS $$

FOR rec IN SELECT * FROM getlist($1)
LOOP
IF somefield = $2 THEN
RETURN NEXT rec;
END IF;
END LOOP;

This doesn't look like a good idea to me, like I'm not letting the
query engine do what it's best at. Another thought I've had
is to have the functions build up a query string then EXECUTE it,
but this gets tedious. Maybe this is a job for CURSORs?

Thanks for any feedback on this

-karl

PS Sorry if this is rambly
PPS Would this type of question be better on Novice?

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

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

Joshua D. Drake

2006-03-29, 8:26 pm

> MY application sends XML requests to a perl script, which has to
> parse them and turn them into queries which get sent off, then the
> results are put back into XML, and sent back to the client.

This doesn't answer your question... but why not just use plPerl?

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

karly@kipshouse.org

2006-03-29, 8:26 pm

On Wed, Mar 29, 2006 at 12:19:03PM -0800, Joshua D. Drake wrote:
>
>
> This doesn't answer your question... but why not just use plPerl?

Reasonable question that I asked and my co-developer asked.

Well the Perl middleware are CGI scripts, and some times do things
other than DB queries, so they need to be outside the DB. I guess
in DB Client/Server parlance the CGI scripts are the client.

As to why PL/SQL instead of PL/Perl, I inherited the DB work by
default, and I had some examples in PL/SQL, so I continued with
that before I discovered PL/Perl. Since the two developers working
on this are comfortable with Perl, we'll be investigating porting
everything to PL/Perl, but for now we just need to get a functional
prototype ASAP.

-karl

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links