#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12

    Oracle to PostgreSQL: Packages


    In oracle you can have private variables within your package, e.g.

    create or replace package examplepackage as
    procedure p_set_var(val varchar2);
    pragma restrict_references(p_set_var, rnps, rnds, wnds);
    function f_get_var return varchar2;
    pragma restrict_references(f_get_var, wnps, rnds, wnds);
    end examplepackage;
    /
    show errors;

    create or replace package body examplepackage as
    privatevar varchar2(32);
    procedure p_set_var(
    val varchar2
    ) AS
    begin
    privatevar := val;
    end;
    function f_get_var return varchar2 AS
    begin
    return privatevar;
    end;
    end examplepackage;
    /
    show errors;

    execute examplepackage.p_set_var('a');
    select examplepackage.f_get_var from dual;

    This allows you to effectively use session or transaction variables within your code. Is there a way to emulate this in PostgreSQL?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12
    I'm beginning to suspect that this isn't a feature that is supported by PostgreSQL .

    I think that I can emulate this behaviour (in a hoargy way) if I can distinguish between sessions or database connections.

    Is there any PostgreSQL equivalent to Session_ID that is different for each database connection?

    Thanks in advance
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    From what I understand a package is sort of like a convoluted stored procedure (or *function* in PostgreSQL).

    Check the online docs.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    12
    Hmm, I may have misnamed this thread. It might be better called 'accessing session variables'.

    Yes MattR,

    An oracle package is similar to a stored procedure or function in PostgreSQL. But they are not completely the same, as oracle packages can contain variables that persist for the session between procedure calls. I've looked through the online documentation, and maybe I'm stupid or something but I haven't found anything there that indicates you can do this in PostgreSQL. Either session variables aren't supported (beyond current_user etc) or the information is tucked away in a place that I'm not looking.

    If you look to my first post, I am wanting to be able to execute

    PHP Code:
    execute examplepackage.p_set_var('a');
    select examplepackage.f_get_var from dual; (returning 'a'
    for multiple sessions. Sorry if this was not clear.

    So, if I have two sessions running simultaneously,
    in session1 the statements
    PHP Code:
    execute examplepackage.p_set_var('a');
    select examplepackage.f_get_var from dual
    will return 'a'

    session2 (database connection) calls
    PHP Code:
    execute examplepackage.p_set_var('b');
    select examplepackage.f_get_var from dual
    gets 'b' out

    These sessions execute at the same time.

    If for example, p_set_var inserted or updated a table value, there is no guarantee that the session1 f_get_var call will return 'a' (it might return 'b' instead, if the session2 p_set_var has already executed). This is why simply inserting values into tables is inadequate as a way of persisting information.

    PostgreSQL must have a session memory space, because values like 'current_user' and 'user' are available. These functions are not fine grained enough for me (what if the same user has two sessions?).

    I am looking for a way to:
    1. Either set session persistent variables, so that subsequent functions and procedures understand the current 'state' of the session (preferred). I am able to do this in oracle with packages.
    2. Failing that, access a unique session identifier so that this information can be stored in a table, references to the session (a hoagy workaround).

    If this is not currently possible in PostgreSQL can someone please reply to indicate this?

    Thanks in advance.

IMN logo majestic logo threadwatch logo seochat tools logo