Обсуждение: Setting a psql variable from SQL output
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.
Thanks,
Bobby
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: >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