Thread: Newbie questions, diff between functions and procedures; & why cube functions?

pgAdmin III is quite a nice tool but one thing I can't figure out is what the
difference is between what pgAdmin calls a function and what it calls a
procedure. They have almost the same properties in the edit dialog (but not
quite). I've looked in the postgres and pgAdmin help docs and there seems to
be no trace of discussion on this issue, which seems strange. I have a book
called Beginning Databases with PostgeSQL which in general is very good, but
has the same issue. There is a chapter called 'functions, stored procedures,
and triggers', which makes zero distinction in the text of the chapter
between functions and procs, uses the terms interchangeably...end result is
this topic is utterly unfathomable for a postgres newbie. I'm used to SQL
Server which has user defined functions and stored procedures.

I ask here re this issue because some other pg interfaces don't offer the
distinction between functions and procs. For instance navicat just has
functions.

Not sure this is a pgAdmin question either but why does pg seem to create a
mass of cube and g_cube functions for each new db? They sure clutter up the
place, but maybe they are needed by postgres?

Another 
-- 
View this message in context:
http://www.nabble.com/Newbie-questions%2C-diff-between-functions-and-procedures----why-cube-functions--tf2689810.html#a7500795
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

Hi,

On Thursday 23 November 2006 02:21, novnov wrote:
| pgAdmin III is quite a nice tool but one thing I can't figure out is what
| the difference is between what pgAdmin calls a function and what it calls a
| procedure.

As of PostgreSQL-8.1 function arguments can be declared IN (default), OUT or
INOUT (cf. "CREATE FUNCTION"). If I understand it right, pgAdmin puts a 
function into its "Procedures" section, if it has at least one OUT parameter.

Ciao,
Thomas

-- 
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

On 22 Nov 2006 at 17:21, novnov wrote:

> Not sure this is a pgAdmin question either but why does pg seem to
> create a mass of cube and g_cube functions for each new db? They sure
> clutter up the place, but maybe they are needed by postgres? 

It soundsas if one of the contrib modules may be loaded by default - 
new DBs are created by copying the template1 database, so anything 
that's in template1 is included in new databases also.

There is a procedure, if you don't want this or if you load a heap of 
stuff into template1 by accident, for zapping template1 and re-
creating it in turn from template0 - it's detailed at 
http://www.postgresql.org/docs/techdocs.22

--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

OK, thanks...and I take it that there is nothing about the cube functions
that are inherently needed? I've never touched template1, this is an out of
the box postgres 8.1 windows install.


Raymond O wrote:
> 
> On 22 Nov 2006 at 17:21, novnov wrote:
> 
>> Not sure this is a pgAdmin question either but why does pg seem to
>> create a mass of cube and g_cube functions for each new db? They sure
>> clutter up the place, but maybe they are needed by postgres? 
> 
> It soundsas if one of the contrib modules may be loaded by default - 
> new DBs are created by copying the template1 database, so anything 
> that's in template1 is included in new databases also.
> 
> There is a procedure, if you don't want this or if you load a heap of 
> stuff into template1 by accident, for zapping template1 and re-
> creating it in turn from template0 - it's detailed at 
> http://www.postgresql.org/docs/techdocs.22
> 
> --Ray.
> 
> 
> ----------------------------------------------------------------------
> 
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod@iol.ie
> ----------------------------------------------------------------------
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 
> 

-- 
View this message in context:
http://www.nabble.com/Newbie-questions%2C-diff-between-functions-and-procedures----why-cube-functions--tf2689810.html#a7535268
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

On 24 Nov 2006 at 18:59, novnov wrote:

> OK, thanks...and I take it that there is nothing about the cube
> functions that are inherently needed? I've never touched template1,
> this is an out of the box postgres 8.1 windows install. 

I'm not aware of the the cube functions - others may know what they 
are. Windows installer, at some point in the installation, asks you 
what modules you want included by default, and then loads these into 
template1; any databases you create thereafter will have them as 
well.

I didn't load any of the contrib modules at installation, and there 
are no functions whatever in the public schema of template1 on my 
development machine.

--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

I am still very confused by pgsql's approach to functions vs procedures.

In my testing using pgAdmin III, if I create both In and Out args it's
listed as a proc. If I add only a In or only an Out, both are listed as
functions and the args are 'gone'. Does that make sense?

I am puzzled by the fact that I don't find many references to 'stored
procedures' in the pgsql docs. One of the oft heard reasons to chose pgsql
over mysql is the supposed presence of stored procedures.

The kind of use I have for stored procedures is to pass in a couple params,
use them in an append or update query, possibly call other procedures, and
return some value (success etc).

Thomas wrote that the IN OUT INOUT arg stuff is as of 8.1, that's the
current stable version of pgsql...but pgsql has supposedly had stored
procedures for a long time. It ends up being very confusing to a newbie.

Thanks 





Thomas Pundt wrote:
> 
> Hi,
> 
> On Thursday 23 November 2006 02:21, novnov wrote:
> | pgAdmin III is quite a nice tool but one thing I can't figure out is
> what
> | the difference is between what pgAdmin calls a function and what it
> calls a
> | procedure.
> 
> As of PostgreSQL-8.1 function arguments can be declared IN (default), OUT
> or
> INOUT (cf. "CREATE FUNCTION"). If I understand it right, pgAdmin puts a 
> function into its "Procedures" section, if it has at least one OUT
> parameter.
> 
> Ciao,
> Thomas
> 
> -- 
> Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 
> 

-- 
View this message in context:
http://www.nabble.com/Newbie-questions%2C-diff-between-functions-and-procedures----why-cube-functions--tf2689810.html#a7544188
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

novnov написа:
> I am still very confused by pgsql's approach to functions vs procedures.
> 
> In my testing using pgAdmin III, if I create both In and Out args it's
> listed as a proc. If I add only a In or only an Out, both are listed as
> functions and the args are 'gone'. Does that make sense?
> 
> I am puzzled by the fact that I don't find many references to 'stored
> procedures' in the pgsql docs. One of the oft heard reasons to chose pgsql
> over mysql is the supposed presence of stored procedures.

Well, stored procedures in Postgres are implemented as server-side
functions (that could be written in several different languages). They
are discussed at length in chapter 35 ("Procedural Languages") and the
subsequent chapters.

>
> The kind of use I have for stored procedures is to pass in a couple params,
> use them in an append or update query, possibly call other procedures, and
> return some value (success etc).
> 
> Thomas wrote that the IN OUT INOUT arg stuff is as of 8.1, that's the
> current stable version of pgsql...but pgsql has supposedly had stored
> procedures for a long time. It ends up being very confusing to a newbie.
[...]

Yes, the functions (stored procedures) have very long history in
Postgres (unlike some other well-known DBMS). The addition that confuses
you, namely the argument mode (IN, OUT, INOUT), is very recent - they
were introduced in version 8.1.

A functions takes 0 or more parameters and returns a value. Most people
would argue that a procedure is special case for a function that returns
nothing (void, none, null). Sometimes it's useful for a function to
return more than one value. There are several ways to achieve this - one
is to introduce the OUT and INOUT parameters.

I consider the decision to distinguish between functions and procedures
in pgAdmin's UI very unfortunate.


-- 
Milen A. Radev

Milen A. Radev wrote:

> I consider the decision to distinguish between functions and procedures
> in pgAdmin's UI very unfortunate.

It's a historic thing - procedures actually appeared in early versions 
of EnterpriseDB originally where they were considered different objects 
from functions if memory serves. The distinction remained when 8.1 was 
released.

I'm happy to discuss re-engineering that if people want (can't guarantee 
I'll actually get time to change anything myself though).

Regards, Dave.

I've tried to follow the instructions on the pgsql site for replacing
template1 with template0. It gets pretty complicated, because you can't
delete a template db, so you have to convert it to a non-template, then
delete, then proceed with the replacement. So, I did all of that and as far
as I could tell all steps suceeded. But a new db still contains all of the
unwanted functions and types. So I was not successful after all.

I will resort to ininstalling pgsql and reinstalling. I have to say that
this aspect of pgsql is rather difficult to work with. Starting with why
cube and g_cube modules are included by default and ending with the
difficulty of manipulating template1...it could be easier for newbies. 

Would it be possible in a future release of pgAdmin that access to these
settings can be adjusted?


Raymond O wrote:
> 
> On 22 Nov 2006 at 17:21, novnov wrote:
> 
>> Not sure this is a pgAdmin question either but why does pg seem to
>> create a mass of cube and g_cube functions for each new db? They sure
>> clutter up the place, but maybe they are needed by postgres? 
> 
> It soundsas if one of the contrib modules may be loaded by default - 
> new DBs are created by copying the template1 database, so anything 
> that's in template1 is included in new databases also.
> 
> There is a procedure, if you don't want this or if you load a heap of 
> stuff into template1 by accident, for zapping template1 and re-
> creating it in turn from template0 - it's detailed at 
> http://www.postgresql.org/docs/techdocs.22
> 
> --Ray.
> 
> 
> ----------------------------------------------------------------------
> 
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod@iol.ie
> ----------------------------------------------------------------------
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 
> 

-- 
View this message in context:
http://www.nabble.com/Newbie-questions%2C-diff-between-functions-and-procedures----why-cube-functions--tf2689810.html#a7583884
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

On 28 Nov 2006 at 9:42, novnov wrote:

> I will resort to ininstalling pgsql and reinstalling. I have to say
> that this aspect of pgsql is rather difficult to work with. Starting
> with why cube and g_cube modules are included by default and ending
> with the difficulty of manipulating template1...it could be easier for
> newbies. 

Are you using the Windows installer? If so, when you come to the 
screen that asks you what modules you want included by default, make 
sure that none of the check boxes are checked. If you need a specific 
module, you can always add it afterwards by running the necessary sql 
script.

What version of PostgreSQL are you using?

> Would it be possible in a future release of pgAdmin that access to
> these settings can be adjusted?  

The contrib modules are a PostgreSQL feature, not pgAdmin.

--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

Thanks for haning in there with me on this. I know the contrib modules stuff
is pgsql, not pgAdmin, but pgAdmin could probably be extended to allow for
simpler access to the template1 db. I'd guess that it could be loaded just
like any db and altered, though of course should only be exposed by special
request in a pgAdmin menu.

I've not actually reinstalled pgsql (8.1). What I did is to review the
screenshots I took of my installation process, and there wasn't any step (at
least that I captured) for contrib modules. Also, in the windows add/remove
one can reenter the pgsql setup process, which is a cool feature (not all
windows programs offer that reentry point). There is no contrib module
config area there either. It's possible that both my screenshots and the
add/remove programs thing skip the contrib module config section...but as
far as I can tell there is nothing to adjust. I hesitated to remove and then
reinstall pgsql because I thought it might intro some other odd variable
into my pgsql issues...but I will if somehow that does allow access to the
contrib modules that are loaded. I'll test on another pc.



Raymond O wrote:
> 
> On 28 Nov 2006 at 9:42, novnov wrote:
> 
>> I will resort to ininstalling pgsql and reinstalling. I have to say
>> that this aspect of pgsql is rather difficult to work with. Starting
>> with why cube and g_cube modules are included by default and ending
>> with the difficulty of manipulating template1...it could be easier for
>> newbies. 
> 
> Are you using the Windows installer? If so, when you come to the 
> screen that asks you what modules you want included by default, make 
> sure that none of the check boxes are checked. If you need a specific 
> module, you can always add it afterwards by running the necessary sql 
> script.
> 
> What version of PostgreSQL are you using?
> 
>> Would it be possible in a future release of pgAdmin that access to
>> these settings can be adjusted?  
> 
> The contrib modules are a PostgreSQL feature, not pgAdmin.
> 
> --Ray.
> 
> 
> ----------------------------------------------------------------------
> 
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod@iol.ie
> ----------------------------------------------------------------------
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 
> 

-- 
View this message in context:
http://www.nabble.com/Newbie-questions%2C-diff-between-functions-and-procedures----why-cube-functions--tf2689810.html#a7584758
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

On 28 Nov 2006 at 10:29, novnov wrote:

> I've not actually reinstalled pgsql (8.1). What I did is to review the
> screenshots I took of my installation process, and there wasn't any
> step (at least that I captured) for contrib modules. Also, in the

I've had to do that at times, and have never had a problem..... I 
would back up my data all the same, to be on the safe side - see 
pg_dumpall in the docs.

I've just downloaded RC1 of pgsql 8.2, and the steps in the installer 
were as follows:

(i) Select language
(ii) Pre-release warning
(iii) Display of installation notes
(iv) Select components to be installed
(v) Windows service configuration
(vi) Cluster initialization
(vii) Procedural languages to be included by default (i.e. in 
template1)
(viii) Contrib modules to be included by default (i.e. in template1).
(ix) Ready to install.

You must have missed step (viii) on your previous installation - the 
"Cube" module is the one that you seem to have included.

--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

I just found out the same thing, running a test install on another pc. I must
have skipped that step with my screenshots.

Sorry for taking up so much bandwidth on this.


Raymond O wrote:
> 
> On 28 Nov 2006 at 10:29, novnov wrote:
> 
>> I've not actually reinstalled pgsql (8.1). What I did is to review the
>> screenshots I took of my installation process, and there wasn't any
>> step (at least that I captured) for contrib modules. Also, in the
> 
> I've had to do that at times, and have never had a problem..... I 
> would back up my data all the same, to be on the safe side - see 
> pg_dumpall in the docs.
> 
> I've just downloaded RC1 of pgsql 8.2, and the steps in the installer 
> were as follows:
> 
> (i) Select language
> (ii) Pre-release warning
> (iii) Display of installation notes
> (iv) Select components to be installed
> (v) Windows service configuration
> (vi) Cluster initialization
> (vii) Procedural languages to be included by default (i.e. in 
> template1)
> (viii) Contrib modules to be included by default (i.e. in template1).
> (ix) Ready to install.
> 
> You must have missed step (viii) on your previous installation - the 
> "Cube" module is the one that you seem to have included.
> 
> --Ray.
> 
> 
> ----------------------------------------------------------------------
> 
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod@iol.ie
> ----------------------------------------------------------------------
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 
> 

-- 
View this message in context:
http://www.nabble.com/Newbie-questions%2C-diff-between-functions-and-procedures----why-cube-functions--tf2689810.html#a7585261
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.

Dave Page wrote:
> Milen A. Radev wrote:
>> I consider the decision to distinguish between functions and procedures
>> in pgAdmin's UI very unfortunate.
(..)
> I'm happy to discuss re-engineering that if people want (can't guarantee 
> I'll actually get time to change anything myself though).


As I have expressed this preference before, I obviously agree with 
Milen: a single category of "functions" would be preferrable over the 
current distinction between "functions" and "procedures". Two downsides 
that come to mind:

- Newcomers will be confused (as we have seen on this list) and search 
for "procedures" in the docs.

- The seemingly arbitrary separation of functions and "procedures" does 
not help with DB maintainance. Often makes me check two lists in the 
object tree instead of one when I look for a function. I still get 
confused sometimes, and I have been using pgAdmin a LOT. (Yeah, hail to 
the makers! :) )


I don't begrudge former Oracle users their warm and fuzzy feeling when 
finding their old acquaintances ("procedures") here in PG, but maybe we 
can offer them a bowl of punch instead? ;)



Regards
Erwin