Thread: Stored Procedure examples
Hello everyone, I'm a brand new person to postgreSql, but not for databases. I came from Oracle and Sybase areas. I'm struggling to create a stored procedure. I searched on Internet for several hours trying to find a simple example, but didn't find anything. I saw dozens of questions how to create a procedure without any responses. I searched on postgreSql site and found a topic "Stored Procedure Example". But actually, they showed how to write a function on postgreSql database. I understand that sometimes it's possible to use a function instead of a SP and vice versa, but it's still different database objects. Can somebody show me an example of SP for postgreSql? It doesn't have to be complex, but it should be complete. I want to be able cut & paste into editor and compile it without errors. I would like to emphasize that I don't need an example for a function; I have tons of them but I don't have ANY stored procedure example. It would be great if you point me to a site with PostgreSql examples for different Stored Procedures if they actually exist as database objects on postgreSql database. Thank you, Vladimir
Vladimir Zelinski wrote: > I'm struggling to create a stored procedure. I > searched on Internet for several hours trying to find > a simple example, but didn't find anything. I saw > dozens of questions how to create a procedure without > any responses. I searched on postgreSql site and found > a topic "Stored Procedure Example". But actually, they > showed how to write a function on postgreSql database. This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. Did you try using pgAdminIII to create your procedure and see if it works any different a creating an identical function? I am sure others will chime in with reasons why you haven't found any stored procedure examples. My sad guess is that in postgresSQL they may be aliases. -- Walter
On Wed, Feb 14, 2007 at 05:18:10PM -0500, Walter Vaughan wrote: > This may not help, but I noticed using pgAdminIII, you can create a > procedure or a function, but they seem to have the same creation interface > and use the same icon. Way back when I learned that procedures are merely functions that don't return a value. So in that sense procedures are indeed just functions. You obviously mean something else but I'm not sure what. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Walter Vaughan wrote: > > This may not help, but I noticed using pgAdminIII, you can create a > procedure or a function, but they seem to have the same creation > interface and use the same icon. > A procedure is a function that returns null. You'll note if you create a procedure under pgAdminIII, it gets saved under functions, not under procedures. I spent a couple of minutes a few days back trying to find all the procedures I'd just created only to note that they were in fact sitting under functions. I couldn't see them under procedures, but when I tried to create them again I was told they already existed... it was hair pulling stuff there for a couple of minutes. -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert wrote: > Walter Vaughan wrote: >> >> This may not help, but I noticed using pgAdminIII, you can create a >> procedure or a function, but they seem to have the same creation >> interface and use the same icon. >> > > A procedure is a function that returns null. That should have said void of course. > > You'll note if you create a procedure under pgAdminIII, it gets saved > under functions, not under procedures. > > I spent a couple of minutes a few days back trying to find all the > procedures I'd just created only to note that they were in fact sitting > under functions. I couldn't see them under procedures, but when I tried > to create them again I was told they already existed... it was hair > pulling stuff there for a couple of minutes. > -- Paul Lambert Database Administrator AutoLedgers
Walter Vaughan wrote: > Vladimir Zelinski wrote: > >> I'm struggling to create a stored procedure. I >> searched on Internet for several hours trying to find >> a simple example, but didn't find anything. I saw >> dozens of questions how to create a procedure without >> any responses. I searched on postgreSql site and found >> a topic "Stored Procedure Example". But actually, they >> showed how to write a function on postgreSql database. > > This may not help, but I noticed using pgAdminIII, you can create a > procedure or a function, but they seem to have the same creation > interface and use the same icon. > > Did you try using pgAdminIII to create your procedure and see if it > works any different a creating an identical function? pgAdmin defines a stored procedure as: - A function on EnterpriseDB 8.0 or above, written in edbspl. or - A function written in EnterpriseDB or PostgreSQL 8.1 or above with OUT or INOUT arguments. The separation (in pgAdmin) is historic, and comes from EnterpriseDB which treats the two object types more distinctly for Oracle compatibility (and had SPs before PostgreSQL). Regards, Dave.
Dave Page wrote: > pgAdmin defines a stored procedure as: > > - A function on EnterpriseDB 8.0 or above, written in edbspl. Why does EnterpriseDB determine what is a stored procedure in PostgreSQL? Shouldn't that be limited to their own version of pgAdmin? > - A function written in EnterpriseDB or PostgreSQL 8.1 or above with > OUT or INOUT arguments. What sense does that make? > The separation (in pgAdmin) is historic, and comes from EnterpriseDB > which treats the two object types more distinctly for Oracle > compatibility (and had SPs before PostgreSQL). The SQL standard does define procedures distinct from functions, and we might want to add that someday, so that system isn't going to make sense anymore. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Dave Page wrote: >> pgAdmin defines a stored procedure as: >> >> - A function on EnterpriseDB 8.0 or above, written in edbspl. > > Why does EnterpriseDB determine what is a stored procedure in > PostgreSQL? Shouldn't that be limited to their own version of pgAdmin? The community version of pgAdmin has always supported EnterpriseDB features. Are you trying to say we should not do that? And of course, EDB DevStudio does also support SPs (and conversely, community PostgreSQL). >> - A function written in EnterpriseDB or PostgreSQL 8.1 or above with >> OUT or INOUT arguments. > > What sense does that make? The spec says that a procedure may have IN, OUT and INOUT parameters, whilst a function only has IN parameters. It also says that a procedure doesn't return a value whereas a function does. Because PostgreSQL allows return values and IN/OUT/INOUT parameters on the same routine, we use the first part of the definition only when making our distinction. Source: section 4.27, SQL-invoked Routines in SWD-02-Foundation-2003-09 >> The separation (in pgAdmin) is historic, and comes from EnterpriseDB >> which treats the two object types more distinctly for Oracle >> compatibility (and had SPs before PostgreSQL). > > The SQL standard does define procedures distinct from functions, and we > might want to add that someday, so that system isn't going to make > sense anymore. Eh? the spec defines them separately, as does pgAdmin, so if PostgreSQL does it as well pgAdmin's system will no longer make sense? Sure we might have to change how the two are distinguished in future versions, but that's no different from other changes we have to make from version to version. I doubt it's likely to be anything like as much work as the operator family reshuffle has been. Regards, Dave.
Dave Page wrote: > Because PostgreSQL allows return values and IN/OUT/INOUT parameters > on the same routine, we use the first part of the definition only > when making our distinction. > > Source: section 4.27, SQL-invoked Routines in > SWD-02-Foundation-2003-09 That same clause also contains various arguments against pgAdmin's definition. For example, all procedures must be invoked using the CALL statement, which PostgreSQL doesn't have. But that is not the point. If you were writing sqlAdmin, then I'd say you are right. But in PostgreSQL we have made conscious efforts to present all programming interfaces under a uniform "function" label, so I think it does users a disservice if the GUI handles it differently. For that matter, what is supposed to be the practical benefit of this distinction? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Dave Page wrote: >> Because PostgreSQL allows return values and IN/OUT/INOUT parameters >> on the same routine, we use the first part of the definition only >> when making our distinction. >> >> Source: section 4.27, SQL-invoked Routines in >> SWD-02-Foundation-2003-09 > > That same clause also contains various arguments against pgAdmin's > definition. For example, all procedures must be invoked using the CALL > statement, which PostgreSQL doesn't have. But that is not the point. > If you were writing sqlAdmin, then I'd say you are right. But in > PostgreSQL we have made conscious efforts to present all programming > interfaces under a uniform "function" label, so I think it does users a > disservice if the GUI handles it differently. > > For that matter, what is supposed to be the practical benefit of this > distinction? As I said, it's a historical design that came about when EDB first introduced stored procedures. pgAdmin maintained the distinction mainly because many users coming from other DBMSs seem to get confused by the whole functions/SPs thing. I believe our interpretation of the distinction is valid, but I'm neither for or against making that distinction as I can see both sides of the argument from the user perspective. Regards, Dave
Dave Page wrote: > Peter Eisentraut wrote: > > Dave Page wrote: > >> Because PostgreSQL allows return values and IN/OUT/INOUT parameters > >> on the same routine, we use the first part of the definition only > >> when making our distinction. > >> > >> Source: section 4.27, SQL-invoked Routines in > >> SWD-02-Foundation-2003-09 > > > > That same clause also contains various arguments against pgAdmin's > > definition. For example, all procedures must be invoked using the CALL > > statement, which PostgreSQL doesn't have. But that is not the point. > > If you were writing sqlAdmin, then I'd say you are right. But in > > PostgreSQL we have made conscious efforts to present all programming > > interfaces under a uniform "function" label, so I think it does users a > > disservice if the GUI handles it differently. > > > > For that matter, what is supposed to be the practical benefit of this > > distinction? > > As I said, it's a historical design that came about when EDB first > introduced stored procedures. pgAdmin maintained the distinction mainly > because many users coming from other DBMSs seem to get confused by the > whole functions/SPs thing. > > I believe our interpretation of the distinction is valid, but I'm > neither for or against making that distinction as I can see both sides > of the argument from the user perspective. FWIW I've seen several reports of users confused because their functions appeared under "stored procedures", and other users confused because some of their stored procedures were being categorized as functions while others as stored procedures. It's good to know the reason for this. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Dave Page <dpage@postgresql.org> writes:
> ... I doubt it's likely to be anything like as much work as the
> operator family reshuffle has been.
[ blink... ] I would not have thought that the opfamily stuff would
affect pgAdmin at all. Would you mind clarifying what problems it
caused you?
regards, tom lane
> I searched on postgreSql site and found > a topic "Stored Procedure Example". But actually, they > showed how to write a function on postgreSql database. A procedure is a function with a return type of void.
On 2/14/07, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Wed, Feb 14, 2007 at 05:18:10PM -0500, Walter Vaughan wrote: > > This may not help, but I noticed using pgAdminIII, you can create a > > procedure or a function, but they seem to have the same creation interface > > and use the same icon. > > Way back when I learned that procedures are merely functions that don't > return a value. So in that sense procedures are indeed just functions. > You obviously mean something else but I'm not sure what. I thought stored procedures did not run implicitly in transactions like functions do. If that's the case, that's a huge difference....you could do vacuum, large loads, etc. that you currently have to do outside the database. I'm not sure about this though. merlin
Merlin Moncure wrote: > I thought stored procedures did not run implicitly in transactions > like functions do. That has nothing to do with the reality in PostgreSQL. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Tom Lane wrote: > Dave Page <dpage@postgresql.org> writes: >> ... I doubt it's likely to be anything like as much work as the >> operator family reshuffle has been. > > [ blink... ] I would not have thought that the opfamily stuff would > affect pgAdmin at all. Would you mind clarifying what problems it > caused you? > No problems - just changes to the code to allow the user to browse operators and op classes in 8.3, and addition of the new (C++) classes to allow the same for op families. Perfectly normal next-version-support stuff :-) Regards, Dave.