Thread: Difference between function and procedure?
Hi, I'm diving more into depth of the features outside the typical table stuff, which I usually need. I wrote a trigger that fires a function written in PL/Python, both works fine so far. But as I browsed my database via pgAdmin3 I recognized that there is also 'Procedures' under 'public' available, but empty yet. Trying to create a procedure resulted in the same SQL statement like creating a function (CREATE FUNCTION ...). I couldn't see any difference. Also didn't find in the PostgreSQL manual if there is a difference between functions and procedures. Are there any at all? Anastasios PS: Using PostgreSQL 8.1.3 on Windows XP development machine.
On 7/19/06, Anastasios Hatzis <ahatzis@gmx.net> wrote: > Hi, > > I'm diving more into depth of the features outside the typical table > stuff, which I usually need. > > I wrote a trigger that fires a function written in PL/Python, both works > fine so far. > > But as I browsed my database via pgAdmin3 I recognized that there is > also 'Procedures' under 'public' available, but empty yet. Trying to > create a procedure resulted in the same SQL statement like creating a > function (CREATE FUNCTION ...). I couldn't see any difference. Also > didn't find in the PostgreSQL manual if there is a difference between > functions and procedures. Are there any at all? there are differences. PostgreSQL only really supports functions and on the lists they are used interchangably. on the -hacker list about a year back there was a good discussion about implementing true SP and why they would be needed. mainly, SP are not externally transactional, making them useful for maintenance type work, such as vacuum (cant be run from inside a transaction) or multiple statement data load via 'copy' commands. i thnk (not sure) that SP can not be inlined into queries like functions. this would be an important distinction. merlin
Merlin Moncure wrote: > there are differences. PostgreSQL only really supports functions and > on the lists they are used interchangably. on the -hacker list about > a year back there was a good discussion about implementing true SP and > why they would be needed. > > mainly, SP are not externally transactional, making them useful for > maintenance type work, such as vacuum (cant be run from inside a > transaction) or multiple statement data load via 'copy' commands. > > i thnk (not sure) that SP can not be inlined into queries like > functions. this would be an important distinction. > Merlin, thank you for clarifying this. So, given your hint, I didn't miss SP until now, but this will probably change in future. ;-) Anastasios
I saw the same behavior with Functions and Procedures. At first, I could not tell what made one go into the Function folder and one go into the Procedure one. The one thing that clearly distinguishes a Procedure (to be placed into the Procedures folder in PgAdminIII) is if you include an OUT parameter. The first time I wrote a Function with an OUT parameter, I could not find it in the functions folder. I thought at first that it didn't compile. Then I noticed it in the Procedures folder. Probably a lot of other characteristics that make it different, but that is at least one. :)
-Paul
On 7/19/06, Anastasios Hatzis <ahatzis@gmx.net> wrote:
Merlin Moncure wrote:
> there are differences. PostgreSQL only really supports functions and
> on the lists they are used interchangably. on the -hacker list about
> a year back there was a good discussion about implementing true SP and
> why they would be needed.
>
> mainly, SP are not externally transactional, making them useful for
> maintenance type work, such as vacuum (cant be run from inside a
> transaction) or multiple statement data load via 'copy' commands.
>
> i thnk (not sure) that SP can not be inlined into queries like
> functions. this would be an important distinction.
>
Merlin,
thank you for clarifying this. So, given your hint, I didn't miss SP
until now, but this will probably change in future. ;-)
Anastasios
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster