Re: variable substitution in SQL commands
От | bill house |
---|---|
Тема | Re: variable substitution in SQL commands |
Дата | |
Msg-id | 4B72C38A.2020406@bellsouth.net обсуждение исходный текст |
Ответ на | Re: variable substitution in SQL commands (bill house <wchouse@bellsouth.net>) |
Список | pgsql-novice |
bill house wrote: > Tom Lane wrote: >> bill house <wchouse@bellsouth.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=# =================================================
В списке pgsql-novice по дате отправления: