Setting a psql variable from SQL output

On Fri, Aug 26, 2011 at 3:01 PM, Bobby Dewitt <Bdewitt@appriss.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

On 08/26/2011 02:16 PM, Merlin Moncure wrote:

On Fri, Aug 26, 2011 at 3:01 PM, Bobby Dewitt <Bdewitt@appriss.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

-- Maybe something like this
\pset format u
\o /tmp/value_holder
-- psql_script.sql has VERSION_TAG at spot for version value
select version(); --or some substring thereof
\o
\! sed /s/VERSION_TAG/`cat /tmp/value_holder`/ /tmp/psql_script.sql >
/tmp/runnable.sql

But Merlin is right. Do it in the scripting language.

/i /tmp/runnable

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@gmail.com> wrote: