Re: Setting a psql variable from SQL output

I'm using a home-grown Ruby script to execute a given SQL script against
all of the databases in our environment. I could modify the Ruby script
to execute a different SQL script depending on the database version, but
that would require a lot of changes to the Ruby code and would probably
only be useful in a few situations. I'd prefer to do all of the version
specific logic in the SQL script so the Ruby code wouldn't need to be
touched.

I tried Rob's suggestion, and it worked perfectly with a few tweaks. Here
is what I did:

------ Contents of main install script executed by Ruby script
\pset format u
\pset t on
\o version_holder
select substring(version() from 12 for 3);
\o
\! sed s/VERSION_TAG/`cat version_holder`/ dblink_version.sql >
run_dblink_install.sql
\i run_dblink_install.sql
commit;

------ Contents of dblink_version.sql script
\i dblinkVERSION_TAG.sql

As you can see, I'm trying to install dblink in all of our existing
databases (several hundred) depending on if they are 8.4 or 9.0 databases.
I've copied the dblink.sql install scripts to my working directory and
named them dblink8.4.sql and dblink9.0.sql for the different install
scripts. Executing the main install script via my Ruby script works
perfectly!

Thanks for the suggestions!
Bobby

On 8/26/11 4:16 PM, "Merlin Moncure" <mmoncure(at)gmail(dot)com> wrote:

>On Fri, Aug 26, 2011 at 3:01 PM, Bobby Dewitt <Bdewitt(at)appriss(dot)com> wrote:
>> Is there a way to get output from a SQL command or function and have it
>>be
>> set to a variable value in psql? My main goal is to execute a certain
>> script depending on the version of the database server (either 8.4 or
>>9.0).
>> I can dynamically execute a script using the following, but I can't
>>get it
>> to set the myscript variable value based on the version of the database:
>> \set myscript 'psql_script.sql'
>> \i :myscript
>> However, I would like to be able to have the script executed like this:
>> \set myscript = 'select substring(version() from 12 for 5);'
>> \i :myscript
>>
>> I can obviously select the version into a PL/PGSQL variable, but then I
>> can't execute the script from PL/PGSQL.
>
>what's your scripting environment? you're probably better off handling
>this from there...
>
>merlin