Re: plpgsql functions organisation

On 5/2/15 2:32 PM, Adrian Klaver wrote:
> On 05/02/2015 09:53 AM, Yves Dorfsman wrote:
>>
>> I find my plpgsql functions becomes unreadable very quickly. I want to
>> break
>> them up in smaller functions.
>>
>> What is the best way to organised them?
>> Is there any way to define functions inside functions?
>> When I list functions in psql, I can see them all at the same level,
>> is there
>> any way to organise them in packages like with python and other
>> languages, so
>> the smaller functions are hidden away in a package/directory?
>
> The only thing I can think of is to use SCHEMAs;
>
> http://www.postgresql.org/docs/9.4/interactive/sql-createschema.html

You can do a crude form of public vs private methods using schemas; I
frequently define schemas that start with a _ and don't grant USAGE to
general users for those schemas as a way to do that (not defining USAGE
means the schemas won't show up in things like \df). I do wish I could
control visibility separately from USAGE... maybe someday.

As for performance concerns, in 99% of cases code maintainability is
going to be way more important than performance microoptimization. If
you're *that* concerned about performance than plpgsql probably isn't
the right answer anyway.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com