Re: variable substitution in SQL commands

bill house wrote:
> Tom Lane wrote:
>> bill house <wchouse(at)bellsouth(dot)net> writes:
>>> I am trying to learn how to construct SQL commands using information
>>> derived from other SQL commands/querys.
>>> These commands are stored for the moment in a simple text file which
>>> would be executed by the psql client like so:
>>> current_database=# \i sql_command_file.sql
>>
>> This is really getting beyond what you can do usefully with a simple
>> psql text file. My first suggestion would be to see if you can write
>> what you need as a plpgsql function.
>>
>> regards, tom lane
>>
>
> I was afraid you were going to say that. This was the indication that I
> was getting from my reading, but I just wanted to make sure this trip
> was really necessary.
>
> Thanks,
>
> Bill House
>
This is a followup on this subject with my findings re: memory variables
from a close reading of the psql man page.

Also a demonstration of my accidental discovery of the (undocumented?)
ability to chain scripts.

This ability has obvious positive implications.

Thanks

Bill

--######################## zz_test_variable_01.sql ############
-- test of memory variables and echo output in psql
-- based on reading of psql man page

-- also demonstrates file chaining

--set test_var_1
\set test_var_1 'this is test_var_1'

--set test_var_2
\set test_var_2 'this is test_var_2'

--give me a new line
\echo

--echo test_var_1
\echo :test_var_1

--echo test_var_2
\echo :test_var_2

--echo test_var_1 plus attempt to concatenate something
\echo :test_var_1'another string'
--output: this is test_var_1 another string
-- ^note space, get rid of it

\echo :test_var_1:test_var_2
--output: this is test_var_1 this is test_var_2
-- ^note space, get rid of it
\set test_var_3 ':test_var1another string'
\echo :test_var_3
--output: :test_var1another string

\set test_var_4 :test_var_1 '/another string'
\echo :test_var_4
--output: this is test_var_1/another string
--Now that's what I'm talking about
--I should be able to construct paths and file names

--call another script
\i zz_test_variable_01a.sql
---------------------end of zz_test_variable_01.sql

--############### zz_test_variable_01a.sql ############################
-- demonstrates file chaining and availability of memory variables
-- called from zz_test_variable_01.sql

\echo
\echo 'This script is zz_test_variable_01a.sql.'
\echo 'It demonstrates the ability of psql to chain files, one calling
another'
\echo 'with the \i meta command.'
\echo 'It was called by the script zz_test_varible_01.sql'
\echo 'This script can also access memory variables set by the calling
file.'
\echo 'In this case, test_var_4.
\echo :test_var_4
\echo
-----------------------end of zz_test_variable_01a.sql

All of the above yields:
=================================================
world=# \i zz_test_variable_01.sql

this is test_var_1
this is test_var_2
this is test_var_1 another string
this is test_var_1 this is test_var_2
:test_var1another string
this is test_var_1/another string

This script is zz_test_variable_01a.sql.
It demonstrates the ability of psql to chain files, one calling another
with the i meta command.
It was called by the script zz_test_varible_01.sql
This script can also access memory variables set by the calling file.
psql:zz_test_variable_01a.sql:11: unterminated quoted string

this is test_var_1/another string

world=#
=================================================