473,246 Members | 1,812 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Postgreqsl & Package

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.

Nov 12 '05 #1
4 8062
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

Nov 12 '05 #2
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

Nov 12 '05 #3
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

Nov 12 '05 #4
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

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kris | last post by:
Question: How do you create an Installer program using the Package and Deployment Wizard provided by Visual Studio Pro 6.0 (SP5) to include subfolders and their contents. I understand how to...
13
by: Adrian Parker | last post by:
I wrote a VB program on my Windows XP machine. It does multi table queries, you can add/delete and edit records, it all worked very beautifully. I was very careful to validate data, and on my...
1
by: Skip Montanaro | last post by:
This is kinda off-topic for this group, but since I'm packaging Python perhaps not too far OT. I built a Solaris package for Python 2.3.3 then copied it to another system and installed it. ...
4
by: Igor Fedorow | last post by:
Hello all, I have an XML file with an internal DTD which looks roughly like this: <?xml version="1.0"?> <!DOCTYPE root > ]> <root> <node>
0
by: prasat | last post by:
Hi Does anybody work with mouse package for cfd analysis in c++. I would like to decide issues. Please reply --
1
by: Maklar | last post by:
Greetings, When I launch the PDW, I select the project that I wish to package and deploy, and go through the various steps in the wizard to reference files and such. When I reach the end of the...
1
by: RandomElle | last post by:
Can anyone help me out with locating & using the package and deployment wizard for Access 2003? I have searched for HOURS and cannot find the Package & Deployment wizard for Access2003 anywhere on...
3
by: Steve | last post by:
Hi All I downloaded Sql server 2005 express SP2 and attempted to modify the Bootstrapper package files as I did with SP1 When i try to install SQL server as part of my VS 2005 deployment app I...
2
by: patrimith | last post by:
Hi List, I am used to the following with Java: import some.package.MyClass; name = MyClass.class.getName(); The value for name will be "some.package.MyClass". For Python, I find:
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.