Postgreqsl & Package

[Birahim FALL]

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?

I've searched in the mailing lists archives, read many lines about
schema / Package support in Postgresql,
but I'm still not able to concreately figure out if I can use packages.

In clear words, what I want to do is to group procedure and function in
a way as

create package <package_name> as
procedure p1 ......
procedure p2 ....
function f1 .....
function f2 .....
end;
and the be able to do a
select * from a_table where package_name.f1(a_column) > 10;

Thanks for your answer.

Bir.

[Shridhar Daithankar]

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

[Birahim FALL]

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 : pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] De la part de Shridhar
Daithankar
Envoyé : mercredi, 22. octobre 2003 13:24
À : pg***********@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Shridhar Daithankar]

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...s/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
---------------------------(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

[Bruno BAGUETTE]

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 - pg******@baguette.net

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend