Re: stored proc

On 29/09/2011 02:34, J.V. wrote:
> Is it possible to group a bunch of methods and functions into a single file (stored procedures & functions) and have a main method that can be called
> to orchestrate the methods, pass in params, get back results, log to a file?
>
> I know this can be done with Oracle PL/SQL but a simple google on this does not show any examples.

In Oracle is called a package - FWICR there is no direct equivalent to it in postgresql.

> Also where can I find a list of stored proc data structures (hash maps, arrays, lists of arrays) or anything else that would be useful.

If you use windows download pgadmin3 and click the help button - you get a copy or link to the
SQL documentation, including data types.

or go to http://www.postgresql.org/docs/current/static/index.html

http://www.postgresql.org/docs/current/static/datatype.html
may be what you are interested in however I would recommend reading the
*majority* of this document - Pg is not Ora!

If you are migrating from Oracle, one point people tend to emphasise is that unlike oracle
you cannot commit and rollback the current transaction within procedural SQL - this is not
a bug or missing feature, so please dont start asking for it to be added :-)

If you really really want to compartmentialise then you could create schemas representing packages
and reference data and functions prefixed with the schema.

i.e. create function schemaname.functionname ...

but this has one major risk that if your functionname is called without the schemaprefix and
a matching functionname exists in a schema in the search_path, this will be called instead.
Nasty!

My preference is to define a naming scheme such as

XXN_YYY_functionname
where XX is the project
N is P procedure - no return values
F function = returns data
T trigger function
...
and YYY is the "package name" :-)

Jacqui