Skip site navigation (1) Skip section navigation (2)

Re: variable substitution in SQL commands

From: bill house <wchouse(at)bellsouth(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: variable substitution in SQL commands
Date: 2010-02-10 14:32:42
Message-ID: 4B72C38A.2020406@bellsouth.net (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-novice
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=#
=================================================


In response to

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-02-10 17:27:06
Subject: Full text search
Previous:From: Michael WoodDate: 2010-02-09 15:46:02
Subject: Re: Incomplete pg_dump operation

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group