Hello, I’m new to this list, and PostgreSQL.
Although new to PostgreSQL I have been an Oracle DBA in the past, and perhaps this is the problem. I keep trying to do things that are simple in Oracle, and I feel like I’m banging my head against the wall!
First, I am developing on a windows vista laptop. VERSION = 'PostgreSQL 8.4.3, compiled by Visual C++ build 1400, 32-bit'. I have installed ActivePerl to implement the language PLPERL.
Question 1:
I understand there are no packages, and thus no global variables in Postgres. That’s fine, because section 40.4 of the PostgreSQL comprehensive documentation shows how to do this using PLPERL (http://www.postgresql.org/docs/8.3/interactive/plperl-global.html). The examples compiles fine for me, but the output does not behave as anticipated:
prod1=# select util.set_var('foo','bar');
set_var
---------
ok
(1 row)
prod1=# select util.get_var('foo');
get_var
-----------------
CODE(0x1738674)
(1 row)
I have no idea what the get_var output means. Note that the example usage of “set_var” in the documentation has a typo (Three single quotes), and is therefore unexecutable, so I’m not convinced that this section of the documentation was highly QA’d... Has anyone made this PL/Perl script work, or found another method to implement global values? I need to dynamically create and store probably a few hundred global values of all datatypes.
Question 2:
When operating interactively inside sqlplus, I was able to pass parameters into scripts. Eg. “@myscript.sql arg1 arg2”. I cannot seem to do this when operating interactively inside psql. Eg. “\i myscript.sql arg1 arg2” returns the error “\i: extra argument "arg1" ignored”, “\i: extra argument "arg2" ignored”, etc.
I do not want to use \set to “preset” my interactive parameters. I *could* write an external perl script that would use the –c flag to interactively pass in \set parameters, but I would prefer to keep everything as conceptually simple as possible for my analysts.
Any suggestions on how to address these two problems (implementing global/session variables, and passing in parameters to SQL scripts), please let me know. If there is a more appropriate place for me to ask these questions, also let me know.
Thanks in advance,
Daniel
