On Fri, Jun 5, 2009 at 1:58 PM, Jean Hoderd<jhoderd@yahoo.com> wrote:
>
> Hi,
>
>> So I don't recommend you try to do this. What is the actual problem
>> you are trying to solve? Why do you want the client library to be
>> concerned with attnotnull at all?
>
> In general the client library needs to check attnotnull to make sure
> that the client is not making a type mistake. If for example a field
> is "int null", but the client program treats it as "int not null" (ie,
> forgetting to check for the null case), then the client program is
> wrong and the compiler should issue a warning. This is basic, essential,
> functionality. But I'm curious: how else could this be done?...
>
>> ... or even more to the point, why do you think the above is a good
>> idea to begin with? It looks more like the kind of bad design that
>> is frequently committed by people who basically don't like SQL, and
>> try to ensure that no one else will like it either.
>
> The above example was absurdly simple, but in the real world the query
> is complex enough that instead of being just a SELECT is actually the
> return of a PL/PGSQL function. I just want a way to tell the client
> which fields from the return type are actually, really, nullable...
I agree with Tom...here is what I see as good reasons to get the app
to call functions:
*) chain together sequence of statements with dependant inputs and outputs
*) interface to a library hooked to the database that you dont want to
expose to the app for some reason, like dblink, or advisory locks
*) it's difficult or problematic to change/recompile the client side code
OTOH, if you are simply hiding one-liner select statements, you should
strongly consider using views instead of functions where possible.
The internal workings of views are more exposed to the planner at
query time and you are not forcing a particular method of invocation
to the app. If you are wrapping updates and inserts (especially
updates), you are likely just creating work for yourself...consider
moving data dependent code into triggers.
sql functions are pretty inflexible...even with recent even with
recent advancements like varargs and default parameters they are
designed to do a very particular thing...and insert/update tend to be
fairly generic in how they operate.
merlin