Re: [GENERAL] plpgsql functions organisation

On May 4, 2015 02:32:14 PM Yves Dorfsman wrote: > > 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. >

Re: [GENERAL] plpgsql functions organisation

> > 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. Isn't one of the advantage of running on the server

Re: [GENERAL] 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

Re: [GENERAL] plpgsql functions organisation

On 05/03/2015 07:14 AM, Melvin Davidson wrote: The point was to show that yes, function calls take time, and using sub functions take even more time. I am not about to write an additional more detailed example just to show the same results. If you are in doubt, I respectfully suggest you do your

Re: [GENERAL] plpgsql functions organisation

The point was to show that yes, function calls take time, and using sub functions take even more time. I am not about to write an additional more detailed example just to show the same results. If you are in doubt, I respectfully suggest you do your own testing. On Sun, May 3, 2015 at 5:26 AM, Alb

Re: [GENERAL] plpgsql functions organisation

> On 03 May 2015, at 2:56, Melvin Davidson wrote: > > OK, Here is a simple example that shows the difference between using a self > contained function and > one that calls sub functions. > > After loading all the functions below, repeat each of the EXPLAIN statements > a few times and note

Re: [GENERAL] plpgsql functions organisation

OK, Here is a simple example that shows the difference between using a self contained function and one that calls sub functions. After loading all the functions below, repeat each of the EXPLAIN statements a few times and note that callsubs takes almost TWICE as long to execute as nosub. CREATE

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 03:28 PM, Bill Moran wrote: On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver wrote: On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/02/2015 10:12 AM, Melvin Davidson wrote: AF

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 03:10 PM, Melvin Davidson wrote: Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load the

Re: [GENERAL] plpgsql functions organisation

On Sat, 02 May 2015 15:06:24 -0700 Adrian Klaver wrote: > On 05/02/2015 02:07 PM, Jeff Janes wrote: > > On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver > > wrote: > > > > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > > > > AFAIK, you cannot "package

Re: [GENERAL] plpgsql functions organisation

Further to the point of saying functions are ineffiencent, consider the fact that as of the current version of PostgreSQL, plpgsql functions cannot be pre-optimized. So when they are referenced in a SQL statement, PostgreSQL (optimizer) has load the function from the catalogs, which involves overhe

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 02:07 PM, Jeff Janes wrote: On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/02/2015 10:12 AM, Melvin Davidson wrote: AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a functio

Re: [GENERAL] plpgsql functions organisation

On Sat, 2 May 2015 14:07:31 -0700 Jeff Janes wrote: > On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver > wrote: > > > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > > > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible > >> to > >> call a function from within a function

Re: [GENERAL] plpgsql functions organisation

On Sat, May 2, 2015 at 1:05 PM, Adrian Klaver wrote: > On 05/02/2015 10:12 AM, Melvin Davidson wrote: > >> AFAIK, you cannot "package" functions in PostgreSQL, but it is possible >> to >> call a function from within a function. >> >> That being said, I would seriously look at how and why you are

Re: [GENERAL] plpgsql functions organisation

On 05/02/2015 10:12 AM, Melvin Davidson wrote: AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not

Re: [GENERAL] plpgsql functions organisation

On 2015-05-02 11:12, Melvin Davidson wrote: > AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to > call a function from within a function. > > That being said, I would seriously look at how and why you are writing your > functions > as functions that call other functions a

Re: [GENERAL] plpgsql functions organisation

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 al

Re: [GENERAL] plpgsql functions organisation

AFAIK, you cannot "package" functions in PostgreSQL, but it is possible to call a function from within a function. That being said, I would seriously look at how and why you are writing your functions as functions that call other functions are not very efficient. Also note that PostgreSQL allows

[GENERAL] plpgsql functions organisation

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 organi

Re: [GENERAL] Plpgsql functions with output parameters

On Thursday 4. June 2009, Radcon Entec wrote: >I have been beating my head against the documentation on plpgsql > functions with output parameters for the last three hours, but I > haven't been able to get them to work yet. > > >I am playing with the sum_n_product function, taken from the > doucmen

[GENERAL] Plpgsql functions with output parameters

I have been beating my head against the documentation on plpgsql functions with output parameters for the last three hours, but I haven't been able to get them to work yet. I am playing with the sum_n_product function, taken from the doucmentation: -- Function: sum_n_product(integer, integer)

Re: [GENERAL] plpgsql functions

2008/8/15 c k <[EMAIL PROTECTED]>: > Hi, > I am getting an error for a function written in plpgsql, as - > > CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) > RETURNS SETOF uf_closingbal AS > $BODY$begin > select accgroups."accgroupid", COALESCE(sum(osc),0) as obc, > COAL

Re: [GENERAL] plpgsql functions

On Aug 15, 2008, at 1:47 PM, Raymond O'Donnell wrote: For functions return SETOF any type, you need to use the following idiom: Or, you can use, RETURN QUERY -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgr

Re: [GENERAL] plpgsql functions

On 15/08/2008 20:12, c k wrote: Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS For functions return SETOF any type, you need to use the following idiom: ... declare

[GENERAL] plpgsql functions

Hi, I am getting an error for a function written in plpgsql, as - CREATE OR REPLACE FUNCTION uf_accgroupbal(date, date, p_company integer) RETURNS SETOF uf_closingbal AS $BODY$begin select accgroups."accgroupid", COALESCE(sum(osc),0) as obc, COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as s

[GENERAL] plpgsql functions or queries

Is it safe to use plpgsql functions with 'security definer'? For example we have table for spamassassin preferences, and user spamassassin. I don't want spamassassin user to see user database (passwords, ...) So I use function: CREATE OR REPLACE FUNCTION get_sa_preferences( VARCHAR ) RETURNS SET

Re: [GENERAL] plpgsql functions and the planner

"Matthew Dennis" <[EMAIL PROTECTED]> writes: > Do SQL statements inside of plpgsql functions get planned upon every > execution, only when the function is first executed/defined, or something > else entirely? First executed per session. > Now, when bar is executed again, will PG (8.3.1) know th

Re: [GENERAL] plpgsql functions and the planner

On Sun, Apr 27, 2008 at 2:06 AM, Matthew Dennis <[EMAIL PROTECTED]> wrote: > Do SQL statements inside of plpgsql functions get planned upon every > execution, only when the function is first executed/defined, or something > else entirely? They are planned on first execution and the plan is cached

[GENERAL] plpgsql functions and the planner

Do SQL statements inside of plpgsql functions get planned upon every execution, only when the function is first executed/defined, or something else entirely? For example, suppose I have a table foo and a function bar. Function bar executes some SQL statements (select/insert/update) against table