ODed on overloads

From: karly(at)kipshouse(dot)org
To: pgsql-general(at)postgresql(dot)org
Subject: ODed on overloads
Date: 2006-03-29 20:12:32
Message-ID: 20060329121215.A25671@kipshouse.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Antimon 2006-03-29 20:16:12 Re: PostgreSQL client api
Previous Message Simon Riggs 2006-03-29 20:12:16 Re: [SQL] Flight numbers data