Thread: Stored procs: PL/Tcl only? Settable privs for them?

I'm trying to figure out how to do in Postgres what I already pretty well understand in
DB2: Create a stored procedure that accepts a couple of arguments, does a look-up
in a table using those args in a where clause, and then return a boolean result of
whether a matching row was found. Or potentially return a row.

I have a few questions:

1) Is PL/Tcl the only way to do stored procedures?

2) Is CREATE FUNCTION pretty much a logical equivalent to CREATE
PROCEDURE in other RDBMSs?
   Is it considered part of the SQL command set to Postgres?

3) Am I right in guessing that it would be a very big job to support Java under
CREATE LANGUAGE?

4) How about granting rights to stored procedures?
   In DB2 one can grant privileges to what are called packages. Well, a stored proc
is like a package. One can grant privileges to a stored proc to have access to a
table or view and what it can do on that table and view.
   Then in DB2 one can grant access for a group or user to invoke a stored proc.
   I don't see where the Postgres GRANT can do that:
   http://postgresql.nextpath.com/docs/user/sql-grant.htm

   A) So is there a way to assign access privileges to a stored proc (ie function) that
are independent of the user who is calling it?
   B) And can one do grants on which users can actually call a particular function?

On Tue, 11 Jul 2000, Randall Parker wrote:

> I'm trying to figure out how to do in Postgres what I already pretty well understand in
> DB2: Create a stored procedure that accepts a couple of arguments, does a look-up
> in a table using those args in a where clause, and then return a boolean result of
> whether a matching row was found. Or potentially return a row.
>
> I have a few questions:
>
> 1) Is PL/Tcl the only way to do stored procedures?

 PL/SQL, C, Perl, PL/Tcl

> 2) Is CREATE FUNCTION pretty much a logical equivalent to CREATE
> PROCEDURE in other RDBMSs?

I not sure how it is in other DBs, but a little differention is here; in
current state is not available create routines that retuns tuple.

> 3) Am I right in guessing that it would be a very big job to support Java under
> CREATE LANGUAGE?

What is a "big job"? :-) You must write some handler for backend SPI (server
programming interface) and some other things for your language. See beckend
source.

> 4) How about granting rights to stored procedures?

 Now not, but in new ACL system (in 7.2?) it will probably possible.


                Karel

Hi,


> > 2) Is CREATE FUNCTION pretty much a logical equivalent to CREATE
> > PROCEDURE in other RDBMSs?
>
> I not sure how it is in other DBs, but a little differention is here; in
> current state is not available create routines that retuns tuple.

Stored procs can returns tuples... but I'd like to know when can a
stored proc return a set not just a tuple?  I'd love to have stored
procs returning large record sets, or storing a huge query with multiple
set differences and subselects in a sql stored proc that way the query
plan can be done only once...

-ryan

--
Ryan Rawson
System Administrator
Binary Environments Ltd.
ryan@bel.bc.ca