Re: [GENERAL] plpgsql functions organisation

[Jan de Visser]

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.
> Isn't one of the advantage of running on the server to avoid data round
> trip?
> 
> What would you recommend for better performance?

You can run python or perl on the server. That's what Jim meant with 'If 
you're *that* concerned about performance than plpgsql probably isn't the 
right answer anyway.'

Additionally: Many moons ago I did extensive and aggressive performance 
analysis on a system that did many recursive queries. This was before CTEs 
(WITH statements) and we settled on recursive plpgsql functions. The queries 
in the functions were trivial, but nevertheless the bottleneck was in the 
query and data processing, and never in the surrounding infrastructure.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Yves Dorfsman]

> 
> 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 to avoid data round trip?

What would you recommend for better performance?

-- 
http://yves.zioup.com
gpg: 4096R/32B0F416



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Jim Nasby]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Adrian Klaver]

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 own testing.


Can't resist a challenge. I took an existing function that calculates an 
aggregated attendance count for a student or all enrolled students over 
a date period and modified it to call sub functions. There are two sub 
functions, one that finds the students enrolled over a period(which by 
the way calls another function) and dates range they where enrolled. The 
other calculates the aggregate values for each student. The original 
function is student_attendance, the modified student_attendance_sub. The 
results are below, where the first argument is the student_id(where 0 
equals all students). The all students version returns 600 rows, the 
single student 16 rows.



hplc=> select * from student_attendance(0, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.204865s
hplc=> select * from student_attendance_sub(0, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.014101s
hplc=> select * from student_attendance(0, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.041182s
hplc=> select * from student_attendance_sub(0, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.011385s
hplc=> select * from student_attendance(0, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.040762s
hplc=> select * from student_attendance_sub(0, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.016506s

hplc=> select * from student_attendance(1, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.00291s
hplc=> select * from student_attendance_sub(1, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.004125s
hplc=> select * from student_attendance(1, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.001907s
hplc=> select * from student_attendance_sub(1, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.003476s
hplc=> select * from student_attendance(1, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.00597s
hplc=> select * from student_attendance_sub(1, '2005-01-17', '2008-01-31');
NOTICE:  Time 00:00:00.003986s

Definite difference in the all students run, probably because one of the 
called functions is used in a LOOP and caching applies.





On Sun, May 3, 2015 at 5:26 AM, Alban Hertroys mailto:haram...@gmail.com>> wrote:


> On 03 May 2015, at 2:56, Melvin Davidson mailto:melvin6...@gmail.com>> 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 that
> callsubs takes almost TWICE as long to execute as nosub.
>
> CREATE OR REPLACE FUNCTION nosub(text)
>   RETURNS void AS
> $BODY$
...
> IF LENGTH(p_in_str) <= 6
> THEN RAISE NOTICE 'Hi %', p_in_str;
> ELSE
> RAISE NOTICE 'Hello %', p_in_str;
> END IF;
>
> RETURN;
> END;
> $BODY$
…


>  CREATE OR REPLACE FUNCTION called1(text)
>   RETURNS void AS
> $BODY$
...
> RAISE NOTICE 'Hi %', p_in_str1;
>
> RETURN;
> END;
> $BODY$
…

>  CREATE OR REPLACE FUNCTION called2(text)
>   RETURNS void AS
> $BODY$
...
> RAISE NOTICE 'Hello %', p_in_str2;
>
> RETURN;
> END;
...


That's a rather uninteresting experiment, as all it does is call a
function and raise a notice. Relative to what the functions do, the
function call itself takes a significant amount of time. No surprise
there, you'll see something similar in any language, even C. All
you're showing is that calling a function takes some amount of time > 0.

In C, a function call needs to look up an address to jump to, in
plpgsql the database needs to look up the function body in a table.
If the function is small and atomic it often gets called from
multiple other functions and is probably cached anyway. The main
difference between C and plpgsql here is that the latter is an
interpreted language, so it does need to read in the entire function
body after a call - which I'd expect to be quite a bit faster with a
smaller (atomic) function body, especially when it hasn't been
cached yet.

So far I haven't been convinced.

An actual use-case where the functions actually do something would
be far more interesting. I doubt anybody writes functions just to
raise a notice. I expect that in reality most plpgsql functions
perform database queries and do something with the result. In such
cases, function call overhead could be significant if the call is
done for each record in a result set, for example. And even then
it's worth considering whethe

Re: [GENERAL] plpgsql functions organisation

[Melvin Davidson]

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, Alban Hertroys  wrote:

>
> > 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 that
> > callsubs takes almost TWICE as long to execute as nosub.
> >
> > CREATE OR REPLACE FUNCTION nosub(text)
> >   RETURNS void AS
> > $BODY$
> ...
> > IF LENGTH(p_in_str) <= 6
> > THEN RAISE NOTICE 'Hi %', p_in_str;
> > ELSE
> > RAISE NOTICE 'Hello %', p_in_str;
> > END IF;
> >
> > RETURN;
> > END;
> > $BODY$
> …
>
>
> >  CREATE OR REPLACE FUNCTION called1(text)
> >   RETURNS void AS
> > $BODY$
> ...
> > RAISE NOTICE 'Hi %', p_in_str1;
> >
> > RETURN;
> > END;
> > $BODY$
> …
>
> >  CREATE OR REPLACE FUNCTION called2(text)
> >   RETURNS void AS
> > $BODY$
> ...
> > RAISE NOTICE 'Hello %', p_in_str2;
> >
> > RETURN;
> > END;
> ...
>
>
> That's a rather uninteresting experiment, as all it does is call a
> function and raise a notice. Relative to what the functions do, the
> function call itself takes a significant amount of time. No surprise there,
> you'll see something similar in any language, even C. All you're showing is
> that calling a function takes some amount of time > 0.
>
> In C, a function call needs to look up an address to jump to, in plpgsql
> the database needs to look up the function body in a table. If the function
> is small and atomic it often gets called from multiple other functions and
> is probably cached anyway. The main difference between C and plpgsql here
> is that the latter is an interpreted language, so it does need to read in
> the entire function body after a call - which I'd expect to be quite a bit
> faster with a smaller (atomic) function body, especially when it hasn't
> been cached yet.
>
> So far I haven't been convinced.
>
> An actual use-case where the functions actually do something would be far
> more interesting. I doubt anybody writes functions just to raise a notice.
> I expect that in reality most plpgsql functions perform database queries
> and do something with the result. In such cases, function call overhead
> could be significant if the call is done for each record in a result set,
> for example. And even then it's worth considering whether that matters to
> your situation enough that it outweighs the usual benefits of code
> separation.
>
>
> > On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver 
> wrote:
> > 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  > > 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
> >  writing
> >  your functions
> >  as functions that call other functions are not very efficient.
> >
> >
> >  I am not following. That is what packaging is about, separating out
> >  'units of work' so they can be combined as needed. Part of that is
> >  using existing functions in new functions/classes. In fact in the
> >  Postgres source I see this in many places. Now it is entirely
> >  possible I missed a memo, so I am open to a more detailed
> >  explanation of the inefficiencies involved.
> >
> >
> > The Postgres source is written in C, not in plpgsql.  C has a good
> > optimizing compiler and plpgsql doesn't.
> >
> > Does this actually matter?  I am a biologist that backed into computing,
> > so I realize I am weak on the fundamentals. Still the scientist in me
> > wants data backing assertions. As I understand it plpgsql works close to
> > the server and is optimized to do so.  I know writing in C would be a
> > better solution. Still is calling plpgsql functions inside plpgsql
> > really a bad thing when just considering plpgsql?
> >
> > The answer to that is the same answer to so many other things: it
> depends.
> >
> > plpgsql functions are slower than C. They also lack a lot of language
> > features that C has. That being said, if they're meeting your needs, then
> > don't worry about it. plpgsql is around because for most people, it works
> > well enough. There are certainly cases when you want to create very
> complex
> > logic in the database and plpgsql is liable to make that difficu

Re: [GENERAL] plpgsql functions organisation

[Alban Hertroys]

> 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 that
> callsubs takes almost TWICE as long to execute as nosub.
> 
> CREATE OR REPLACE FUNCTION nosub(text)
>   RETURNS void AS
> $BODY$
...
> IF LENGTH(p_in_str) <= 6
> THEN RAISE NOTICE 'Hi %', p_in_str;
> ELSE
> RAISE NOTICE 'Hello %', p_in_str;
> END IF;
> 
> RETURN;
> END;
> $BODY$
…


>  CREATE OR REPLACE FUNCTION called1(text)
>   RETURNS void AS
> $BODY$
...
> RAISE NOTICE 'Hi %', p_in_str1;
> 
> RETURN;
> END;
> $BODY$
…

>  CREATE OR REPLACE FUNCTION called2(text)
>   RETURNS void AS
> $BODY$
...
> RAISE NOTICE 'Hello %', p_in_str2;
> 
> RETURN;
> END;
...


That's a rather uninteresting experiment, as all it does is call a function and 
raise a notice. Relative to what the functions do, the function call itself 
takes a significant amount of time. No surprise there, you'll see something 
similar in any language, even C. All you're showing is that calling a function 
takes some amount of time > 0.

In C, a function call needs to look up an address to jump to, in plpgsql the 
database needs to look up the function body in a table. If the function is 
small and atomic it often gets called from multiple other functions and is 
probably cached anyway. The main difference between C and plpgsql here is that 
the latter is an interpreted language, so it does need to read in the entire 
function body after a call - which I'd expect to be quite a bit faster with a 
smaller (atomic) function body, especially when it hasn't been cached yet.

So far I haven't been convinced.

An actual use-case where the functions actually do something would be far more 
interesting. I doubt anybody writes functions just to raise a notice. I expect 
that in reality most plpgsql functions perform database queries and do 
something with the result. In such cases, function call overhead could be 
significant if the call is done for each record in a result set, for example. 
And even then it's worth considering whether that matters to your situation 
enough that it outweighs the usual benefits of code separation.


> On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver  
> wrote:
> 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  > 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
>  writing
>  your functions
>  as functions that call other functions are not very efficient.
> 
> 
>  I am not following. That is what packaging is about, separating out
>  'units of work' so they can be combined as needed. Part of that is
>  using existing functions in new functions/classes. In fact in the
>  Postgres source I see this in many places. Now it is entirely
>  possible I missed a memo, so I am open to a more detailed
>  explanation of the inefficiencies involved.
> 
> 
> The Postgres source is written in C, not in plpgsql.  C has a good
> optimizing compiler and plpgsql doesn't.
> 
> Does this actually matter?  I am a biologist that backed into computing,
> so I realize I am weak on the fundamentals. Still the scientist in me
> wants data backing assertions. As I understand it plpgsql works close to
> the server and is optimized to do so.  I know writing in C would be a
> better solution. Still is calling plpgsql functions inside plpgsql
> really a bad thing when just considering plpgsql?
> 
> The answer to that is the same answer to so many other things: it depends.
> 
> plpgsql functions are slower than C. They also lack a lot of language
> features that C has. That being said, if they're meeting your needs, then
> don't worry about it. plpgsql is around because for most people, it works
> well enough. There are certainly cases when you want to create very complex
> logic in the database and plpgsql is liable to make that difficult. But
> there are a lot of cases where having to manage pointers and a build
> environment and all the things that go with C aren't justified, because
> plpgsql has none of that complexity. There are advantages both ways.
> 
> The beauty of PostgreSQL is that you have both available and you
> can choose whichever is best for your situation.
> 
> Agreed, though in my case I drop into plpythonu when I want more complex 
> solutions.
> 
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
> 
> 
> -- 
> 

Re: [GENERAL] plpgsql functions organisation

[Melvin Davidson]

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 OR REPLACE FUNCTION nosub(text)
  RETURNS void AS
$BODY$

DECLARE

p_in_strALIAS FOR $1;

BEGIN
IF LENGTH(p_in_str) <= 6
THEN RAISE NOTICE 'Hi %', p_in_str;
ELSE
RAISE NOTICE 'Hello %', p_in_str;
END IF;

RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION nosub(text)
  OWNER TO postgres;

 CREATE OR REPLACE FUNCTION called1(text)
  RETURNS void AS
$BODY$

DECLARE

p_in_str1ALIAS FOR $1;

BEGIN
RAISE NOTICE 'Hi %', p_in_str1;

RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION called1(text)
  OWNER TO postgres;

 CREATE OR REPLACE FUNCTION called2(text)
  RETURNS void AS
$BODY$

DECLARE

p_in_str2ALIAS FOR $1;

BEGIN
RAISE NOTICE 'Hello %', p_in_str2;

RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION called2(text)
  OWNER TO postgres;

 CREATE OR REPLACE FUNCTION callsubs(text)
  RETURNS void AS
$BODY$

DECLARE

p_in_strALIAS FOR $1;

BEGIN
IF LENGTH(p_in_str) <= 6
THEN PERFORM CALLED1(p_in_str);
ELSE
PERFORM CALLED2(p_in_str);
END IF;

RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION callsubs(text)
  OWNER TO postgres;

EXPLAIN ANALYZE SELECT nosub('melvin');

EXPLAIN ANALYZE SELECT callsubs('melvin');

On Sat, May 2, 2015 at 7:37 PM, Adrian Klaver 
wrote:

> 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 <
 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 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.


  I am not following. That is what packaging is about, separating out
  'units of work' so they can be combined as needed. Part of that is
  using existing functions in new functions/classes. In fact in the
  Postgres source I see this in many places. Now it is entirely
  possible I missed a memo, so I am open to a more detailed
  explanation of the inefficiencies involved.


 The Postgres source is written in C, not in plpgsql.  C has a good
 optimizing compiler and plpgsql doesn't.

>>>
>>> Does this actually matter?  I am a biologist that backed into computing,
>>> so I realize I am weak on the fundamentals. Still the scientist in me
>>> wants data backing assertions. As I understand it plpgsql works close to
>>> the server and is optimized to do so.  I know writing in C would be a
>>> better solution. Still is calling plpgsql functions inside plpgsql
>>> really a bad thing when just considering plpgsql?
>>>
>>
>> The answer to that is the same answer to so many other things: it depends.
>>
>> plpgsql functions are slower than C. They also lack a lot of language
>> features that C has. That being said, if they're meeting your needs, then
>> don't worry about it. plpgsql is around because for most people, it works
>> well enough. There are certainly cases when you want to create very
>> complex
>> logic in the database and plpgsql is liable to make that difficult. But
>> there are a lot of cases where having to manage pointers and a build
>> environment and all the things that go with C aren't justified, because
>> plpgsql has none of that complexity. There are advantages both ways.
>>
>> The beauty of PostgreSQL is that you have both available and you
>> can choose whichever is best for your situation.
>>
>
> Agreed, though in my case I drop into plpythonu when I want more complex
> solutions.
>
>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] plpgsql functions organisation

[Adrian Klaver]

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:

 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.


 I am not following. That is what packaging is about, separating out
 'units of work' so they can be combined as needed. Part of that is
 using existing functions in new functions/classes. In fact in the
 Postgres source I see this in many places. Now it is entirely
 possible I missed a memo, so I am open to a more detailed
 explanation of the inefficiencies involved.


The Postgres source is written in C, not in plpgsql.  C has a good
optimizing compiler and plpgsql doesn't.


Does this actually matter?  I am a biologist that backed into computing,
so I realize I am weak on the fundamentals. Still the scientist in me
wants data backing assertions. As I understand it plpgsql works close to
the server and is optimized to do so.  I know writing in C would be a
better solution. Still is calling plpgsql functions inside plpgsql
really a bad thing when just considering plpgsql?


The answer to that is the same answer to so many other things: it depends.

plpgsql functions are slower than C. They also lack a lot of language
features that C has. That being said, if they're meeting your needs, then
don't worry about it. plpgsql is around because for most people, it works
well enough. There are certainly cases when you want to create very complex
logic in the database and plpgsql is liable to make that difficult. But
there are a lot of cases where having to manage pointers and a build
environment and all the things that go with C aren't justified, because
plpgsql has none of that complexity. There are advantages both ways.

The beauty of PostgreSQL is that you have both available and you
can choose whichever is best for your situation.


Agreed, though in my case I drop into plpythonu when I want more complex 
solutions.







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Adrian Klaver]

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
function from the catalogs, which involves overhead. If the function
calls another function, then the process has to be repeated, which
involves additional overhead. Ergo, that is not the most efficient way
of doing things.



Yeah, I see the explanation here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

Which has this:

"As each expression and SQL command is first executed in the function, 
the PL/pgSQL interpreter parses and analyzes the command to create a 
prepared statement, using the SPI manager's SPI_prepare function. 
Subsequent visits to that expression or command reuse the prepared 
statement. Thus, a function with conditional code paths that are seldom 
visited will never incur the overhead of analyzing those commands that 
are never executed within the current session"


So it still not clear to me whether a monolithic function is better or 
worse than one that calls other functions as needed. Probably over 
thinking this, but it would make a good experiment. Just have to figure 
out a realistic scenario to test. Thanks for the input.


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Bill Moran]

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" 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.
> >
> >
> > I am not following. That is what packaging is about, separating out
> > 'units of work' so they can be combined as needed. Part of that is
> > using existing functions in new functions/classes. In fact in the
> > Postgres source I see this in many places. Now it is entirely
> > possible I missed a memo, so I am open to a more detailed
> > explanation of the inefficiencies involved.
> >
> >
> > The Postgres source is written in C, not in plpgsql.  C has a good
> > optimizing compiler and plpgsql doesn't.
> 
> Does this actually matter?  I am a biologist that backed into computing, 
> so I realize I am weak on the fundamentals. Still the scientist in me 
> wants data backing assertions. As I understand it plpgsql works close to 
> the server and is optimized to do so.  I know writing in C would be a 
> better solution. Still is calling plpgsql functions inside plpgsql 
> really a bad thing when just considering plpgsql?

The answer to that is the same answer to so many other things: it depends.

plpgsql functions are slower than C. They also lack a lot of language
features that C has. That being said, if they're meeting your needs, then
don't worry about it. plpgsql is around because for most people, it works
well enough. There are certainly cases when you want to create very complex
logic in the database and plpgsql is liable to make that difficult. But
there are a lot of cases where having to manage pointers and a build
environment and all the things that go with C aren't justified, because
plpgsql has none of that complexity. There are advantages both ways.

The beauty of PostgreSQL is that you have both available and you
can choose whichever is best for your situation.

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Melvin Davidson]

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 overhead. If the function calls
another function, then the process has to be repeated, which involves
additional overhead. Ergo, that is not the most efficient way of doing
things.

On Sat, May 2, 2015 at 5:17 PM, Bill Moran  wrote:

> 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.
> > >>
> > >> 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.
> > >>
> > >
> > > I am not following. That is what packaging is about, separating out
> 'units
> > > of work' so they can be combined as needed. Part of that is using
> existing
> > > functions in new functions/classes. In fact in the Postgres source I
> see
> > > this in many places. Now it is entirely possible I missed a memo, so I
> am
> > > open to a more detailed explanation of the inefficiencies involved.
> > >
> >
> > The Postgres source is written in C, not in plpgsql.  C has a good
> > optimizing compiler and plpgsql doesn't.
>
> Maybe that's a roundabout way of saying that if your functions are
> complex enough to require calling "sub-functions" they might be
> justifying being writting in C?
>
> --
> Bill Moran
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] plpgsql functions organisation

[Adrian Klaver]

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 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.


I am not following. That is what packaging is about, separating out
'units of work' so they can be combined as needed. Part of that is
using existing functions in new functions/classes. In fact in the
Postgres source I see this in many places. Now it is entirely
possible I missed a memo, so I am open to a more detailed
explanation of the inefficiencies involved.


The Postgres source is written in C, not in plpgsql.  C has a good
optimizing compiler and plpgsql doesn't.


Does this actually matter?  I am a biologist that backed into computing, 
so I realize I am weak on the fundamentals. Still the scientist in me 
wants data backing assertions. As I understand it plpgsql works close to 
the server and is optimized to do so.  I know writing in C would be a 
better solution. Still is calling plpgsql functions inside plpgsql 
really a bad thing when just considering plpgsql?





Cheers,

Jeff



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Bill Moran]

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.
> >>
> >> 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.
> >>
> >
> > I am not following. That is what packaging is about, separating out 'units
> > of work' so they can be combined as needed. Part of that is using existing
> > functions in new functions/classes. In fact in the Postgres source I see
> > this in many places. Now it is entirely possible I missed a memo, so I am
> > open to a more detailed explanation of the inefficiencies involved.
> >
> 
> The Postgres source is written in C, not in plpgsql.  C has a good
> optimizing compiler and plpgsql doesn't.

Maybe that's a roundabout way of saying that if your functions are
complex enough to require calling "sub-functions" they might be
justifying being writting in C?

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Jeff Janes]

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 writing
>> your functions
>> as functions that call other functions are not very efficient.
>>
>
> I am not following. That is what packaging is about, separating out 'units
> of work' so they can be combined as needed. Part of that is using existing
> functions in new functions/classes. In fact in the Postgres source I see
> this in many places. Now it is entirely possible I missed a memo, so I am
> open to a more detailed explanation of the inefficiencies involved.
>

The Postgres source is written in C, not in plpgsql.  C has a good
optimizing compiler and plpgsql doesn't.

Cheers,

Jeff

Re: [GENERAL] plpgsql functions organisation

[Adrian Klaver]

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 very efficient.


I am not following. That is what packaging is about, separating out 
'units of work' so they can be combined as needed. Part of that is using 
existing functions in new functions/classes. In fact in the Postgres 
source I see this in many places. Now it is entirely possible I missed a 
memo, so I am open to a more detailed explanation of the inefficiencies 
involved.




Also note that PostgreSQL allows you define functions using Python, so
that might be a possible compromise.

On Sat, May 2, 2015 at 12:53 PM, Yves Dorfsman mailto:y...@zioup.com>> 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?


Thanks.

--
http://yves.zioup.com
gpg: 4096R/32B0F416



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Yves Dorfsman]

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 are not very efficient.

Simply to make long procedures easier to read and follow the logic.

> 
> Also note that PostgreSQL allows you define functions using Python, so that
> might be a possible compromise.


Thanks.

-- 
http://yves.zioup.com
gpg: 4096R/32B0F416



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Adrian Klaver]

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




Thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions organisation

[Melvin Davidson]

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 you define functions using Python, so that
might be a possible compromise.

On Sat, May 2, 2015 at 12:53 PM, 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?
>
>
> Thanks.
>
> --
> http://yves.zioup.com
> gpg: 4096R/32B0F416
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] plpgsql functions organisation

[Yves Dorfsman]

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?


Thanks.

-- 
http://yves.zioup.com
gpg: 4096R/32B0F416



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Plpgsql functions with output parameters

[Leif B. Kristensen]

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
> doucmentation:
>
>-- Function: sum_n_product(integer, integer)
>-- DROP FUNCTION sum_n_product(integer, integer);
>CREATE OR REPLACE FUNCTION sum_n_product(IN x integer, IN y integer,
> OUT sum integer, OUT prod integer) RETURNS record AS
>$BODY$
>BEGIN
>    sum := x + y;
>    prod := x * y;
>END;
>$BODY$
>  LANGUAGE 'plpgsql' VOLATILE;
>
>If I go to a query window and execute "select sum_n_product(2, 3)", I
> get back "(2,3)".  I then wrote a function named "test_sum_n_product"
> to try to retrieve one of the numbers from inside the record (or
> whatever it is) that sum_n_product gave me.

Hint 1: SELECT * FROM sum_n_product(2, 3);

Hint 2: SELECT * FROM sum_n_product(2, 3) INTO foo;
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Plpgsql functions with output parameters

[Radcon Entec]

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)
-- DROP FUNCTION sum_n_product(integer, integer);
CREATE OR REPLACE FUNCTION sum_n_product(IN x integer, IN y integer, OUT sum 
integer, OUT prod integer)
  RETURNS record AS
$BODY$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

If I go to a query window and execute "select sum_n_product(2, 3)", I get back 
"(2,3)".  I then wrote a function named "test_sum_n_product" to try to retrieve 
one of the numbers from inside the record (or whatever it is) that 
sum_n_product gave me.

Here is my first try:

CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
    sum int4;
    product int4;
    result record;
BEGIN
    select into sum, product sum_n_product($1, $2);
    return sum;
END;
$$ LANGUAGE plpgsql;

When I call that function from a query window, I get:
ERROR:  invalid input syntax for integer: "(5,6)"
CONTEXT:  PL/pgSQL function "test_sum_n_product" line 6 at SQL statement

It seems that it's trying to stuff the entire record into the first variable I 
gave it.  So, I tested that by changing the function to:

CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
    sum int4;
    product int4;
    result record;
BEGIN
    select into result sum_n_product($1, $2);
    return 2;
END;
$$ LANGUAGE plpgsql;

This function ran successfully, and returned 2.  While it confirms my 
hypothesis that I need to put what I get back from sum_n_product into a record, 
I'm no closer to getting individual numbers out of the record.

I thought maybe the record's fields would have the names of the output 
parameters, so I tried:
CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
    sum int4;
    product int4;
    result record;
BEGIN
    select into result sum_n_product($1, $2);
    return result.sum;
END;
$$ LANGUAGE plpgsql;

I got:
ERROR:  record "result" has no field "sum"

It seems to me, from working in C++ and C#, that to use an output parameter, I 
should define a place for the value to be written, and then I should pass the 
name of that place in to the function.  That would make my function look like:

CREATE or replace FUNCTION test_sum_n_product(x int4, y int4) returns int4 AS $$
declare
    sum int4;
    product int4;
    result record;
BEGIN
    select into result sum_n_product($1, $2, sum, product);
    return sum;
END;
$$ LANGUAGE plpgsql;

But when I try that, I get:

ERROR:  function sum_n_product(integer, integer, integer, integer) does not 
exist
LINE 1: select sum_n_product( $1 ,  $2 ,  $3 ,  $4 )
   ^
HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.
QUERY:  select sum_n_product( $1 ,  $2 ,  $3 ,  $4 )
CONTEXT:  PL/pgSQL function "test_sum_n_product" line 6 at SQL statement

So, I don't have any more guesses.  How do I work with the record that comes 
back from a function with output parameters?

Thank you very much.

RobR


  

Re: [GENERAL] plpgsql functions

[Pavel Stehule]

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,
> COALESCE(sum(osd),0) as obd, COALESCE(sum(csc),0) as sc,
> COALESCE(sum(csd),0) as sd, (COALESCE(sum(osc),0)+COALESCE(sum(csc),0)) as
> cc, (COALESCE(sum(osd),0)+COALESCE(sum(csd),0)) as cd,
> accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount",
> accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid",
> accgroups."accobjecttype", accgroups."againstid" from (accgroups left join
> (select * from uf_accgroupob($1)) as accob on
> accgroups."accgroupid"=accob."accgroupid") left join (select * from
> uf_accgroupcurrentbal($1, $2)) as accgcb2 on accgroups."accgroupid" =
> accgcb2."accgroupid"  group by accgroups."accgroupid",
> accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount",
> accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid",
> accgroups."accobjecttype", accgroups."againstid";
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100
>   ROWS 1000;
> giving me an error when called from another function as 'query has no
> destination for result data'.
> Why? Please give the details about creating functions having only SELECT
> statements using plpgsql?, What points to be remembered?

pl/pgsql isn't SQL/PSM - so there are not allowed one statement
functions. Use SQL language instead.

Regards
Pavel Stehule

>
> Thanks
> CPK
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions

[Christophe]

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.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions

[Raymond O'Donnell]

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
  rec yourtype;
begin
  for rec in [your query here...]
  loop
return next rec;
  end loop;
  return;
end;
...

See the docs on control structures in pl/pgsql.

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] plpgsql functions

[c k]

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 sc,
COALESCE(sum(csd),0) as sd, (COALESCE(sum(osc),0)+COALESCE(sum(csc),0)) as
cc, (COALESCE(sum(osd),0)+COALESCE(sum(csd),0)) as cd,
accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount",
accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid",
accgroups."accobjecttype", accgroups."againstid" from (accgroups left join
(select * from uf_accgroupob($1)) as accob on
accgroups."accgroupid"=accob."accgroupid") left join (select * from
uf_accgroupcurrentbal($1, $2)) as accgcb2 on accgroups."accgroupid" =
accgcb2."accgroupid"  group by accgroups."accgroupid",
accgroups."localglobal", accgroups."companyid", accgroups."isledgeraccount",
accgroups."accgrouplevel", accgroups."mastergroup", accgroups."accobjectid",
accgroups."accobjecttype", accgroups."againstid";
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
giving me an error when called from another function as 'query has no
destination for result data'.
Why? Please give the details about creating functions having only SELECT
statements using plpgsql?, What points to be remembered?

Thanks
CPK

[GENERAL] plpgsql functions or queries

[Artis Caune]

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 SETOF sa_preferences AS $$
DECLARE
rec RECORD;
BEGIN
SELECT user_id, sa_group
  INTO rec
  FROM users
  JOIN emails USING (user_id)
 WHERE email = $1;

IF FOUND THEN
IF rec.user_id != rec.sa_group AND rec.sa_group != 0 THEN
RETURN QUERY SELECT *
   FROM sa_preferences
  WHERE user_id = rec.sa_group;
END IF;

RETURN QUERY SELECT *
   FROM sa_preferences
  WHERE user_id = rec.user_id;
END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

There is no grants for spamassassin user to tables users, emails.
spamassassin user can get preferences but can't see users database.

If I change or add new features to system, I don't need to change
queries on every server, just replace function.

I want use functions like this for every lookup - domains, users,
forwards, many more
Most important I want to use plpgsql functions for web functions, like
add_email, chenge_password, ...
Is there sql injections possible with those functions, should I check,
quote $1, $2 variables?

How about performance? Is there some overhead when using plpgsql?


How are transactions used within plpgsql functions?
If I run query:
  SELECT * FROM get_sa_preferences('[EMAIL PROTECTED]');
I'm actually running?:
  BEGIN;
  SELECT * FROM get_sa_preferences('[EMAIL PROTECTED]');
  COMMIT;

Can I use:
  BEGIN;
  SELECT * FROM add_user('description');
  SELECT * FROM add_email('[EMAIL PROTECTED]');
  SELECT * FROM add_settings('setting1', 'setting2');
  ... other functions ...
  COMMIT;


thanks,
Artis

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions and the planner

[Gregory Stark]

"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 that a seqscan is no
> longer reasonable?

It won't notice until someone runs ANALYZE on that table. autovacuum should
notice that it's necessary and run but might not run promptly enough for your
purposes. You might have to run analyze within your function.

Before 8.3 it still wouldn't get replanned until you started a new session but
8.3 is more clever.


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql functions and the planner

[Douglas McNaught]

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 for later use.

-Doug

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] plpgsql functions and the planner

[Matthew Dennis]

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 foo using
various indexed columns.  When the function is created and first executed,
table foo is near empty so the statements in function bar use seqscans.
Then table foo is filled with many rows.  Now, when bar is executed again,
will PG (8.3.1) know that a seqscan is no longer reasonable?