Re: Setting a psql variable from SQL output

On 08/26/2011 02:16 PM, Merlin Moncure 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
>
-- 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