Thread: Postgreqsl & Package

create package <package_name> as

    procedure p1 ......

    procedure p2 ....

    function f1 .....

    function f2 .....

end;

On Wednesday 22 October 2003 16:41, Birahim FALL wrote:
> Hi,
> I was an oracle dev/admin and I'm quite new to postgresql.
> Postgresql is great, but something bothers me.
> It seems that there's no concept of PACKAGE in PL/pgSQL as in Oracle
> PL/SQL.
> Is is definitely that? or did I missed something? or is it planned for a
> future version?

You can use scemas instead of packages.  You still might miss few details
w.r.t oracle package but in general that should fill in the gap pretty
nicely.

Check the following

test=# create schema a;
CREATE SCHEMA
test=# create schema b;
CREATE SCHEMA
test=# create function a.test1() returns boolean as 'begin
test'# return true;
test'# end;' language plpgsql;
CREATE FUNCTION
test=# create function b.test1() returns boolean as 'begin
test'# return false;
test'# end;' language plpgsql;
CREATE FUNCTION
test=# select a.test1();
 test1
-------
 t
(1 row)

test=# select b.test1();
 test1
-------
 f
(1 row)

HTH

 Shridhar

Thanks, Shridhar,
I've read something like that in my search this morning!
But doing that you can have only one package in the scope of a schema.
Then in a multipackage project these packages cannot access directly to
the same objects.
In you eg. We could have a table a.people, and it is not in a scope
accessible to b.test1 without a grant statement.
More info is welcomed,
Otherwise thanks a lot.
Bir

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] De la part de Shridhar
Daithankar
Envoyé : mercredi, 22. octobre 2003 13:24
À : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Postgreqsl & Package


On Wednesday 22 October 2003 16:41, Birahim FALL wrote:
> Hi,
> I was an oracle dev/admin and I'm quite new to postgresql. Postgresql
> is great, but something bothers me. It seems that there's no concept
> of PACKAGE in PL/pgSQL as in Oracle PL/SQL.
> Is is definitely that? or did I missed something? or is it planned for
a
> future version?

You can use scemas instead of packages.  You still might miss few
details
w.r.t oracle package but in general that should fill in the gap pretty
nicely.

Check the following

test=# create schema a;
CREATE SCHEMA
test=# create schema b;
CREATE SCHEMA
test=# create function a.test1() returns boolean as 'begin test'# return
true; test'# end;' language plpgsql; CREATE FUNCTION test=# create
function b.test1() returns boolean as 'begin test'# return false; test'#
end;' language plpgsql; CREATE FUNCTION test=# select a.test1();  test1
-------
 t
(1 row)

test=# select b.test1();
 test1
-------
 f
(1 row)

HTH

 Shridhar


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match

On Wednesday 22 October 2003 17:11, Birahim FALL wrote:
> Thanks, Shridhar,
> I've read something like that in my search this morning!
> But doing that you can have only one package in the scope of a schema.
> Then in a multipackage project these packages cannot access directly to
> the same objects.
> In you eg. We could have a table a.people, and it is not in a scope
> accessible to b.test1 without a grant statement.

Yes that is correct. In fact you could look at it as additional security
feature. i.e. functions from a package can only be accessed by a certain
users..:-)

Besides there is only one grant you need per schema, that is usage. Shouldn't
be a such a big problem, isn't it?

I would be surprised if oracle packages did not provide this feature.

> More info is welcomed,

Check schema search path

http://developer.postgresql.org/docs/postgres/sql-set.html

I just checked if I could create nested schemas. That would be way too cool..
but it is not supported..:-(

HTH

 Shridhar

Birahim FALL a écrit :
>It seems that there's no concept of PACKAGE in PL/pgSQL as in Oracle
PL/SQL.
>Is is definitely that? or did I missed something? or is it planned for
a future version?

Hello,

This feature is in the TODO list : Commands --> SERVER-SIDE LANGUAGES
(cf. http://developer.postgresql.org/todo.php) I also hope that will be
added in a future version, I sometimes receive this question from people
that moves from Oracle to PostgreSQL.

There is a workaround for that : You can create schemas and add
functions inside theses schemas. See the Shridhar's anwser :-)

Regards,

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net