Custom personal monitor sql by writing the .psqlrc file I want to custom my personaly monitor sql  by writing the  .psqlrc file ,  but I encounter some problems .

--tablespace

skypcsuit=&gt; \db
                          List of tablespaces
       Name        |  Owner   |                Location                 
-------------------+----------+-----------------------------------------
 pg_default        | postgres | 
 pg_global         | postgres | 
 tbs_db_francs     | postgres | /database/pg92/pg_tbs/tbs_db_francs
 tbs_skypcsuit     | postgres | /database/pg92/pg_tbs/tbs_skypcsuit
 tbs_skypcsuit_idx | postgres | /database/pg92/pg_tbs/tbs_skypcsuit_idx


--.psqlrc

\set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb  where a.relkind in (\'r\', \'i\') and a.reltablespace=tb.oid and tb.spcname=\':v_spcname\' order by a.relpages desc limit 10;'


--call the sql but return null value

skypcsuit=&gt; \set v_spcname tbs_skypcsuit_idx

skypcsuit=&gt; \echo :v_spcname 
tbs_skypcsuit_idx

skypcsuit=&gt; :top10_ts_table 
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+----------
(0 rows)


I set the the value of variable v_spcname to 'tbs_skypcsuit_idx'。

-- csvlog

2014-07-24 01:10:00.091 PDT,"skypcsuit","skypcsuit",2123,"[local]",53d0bf4c.84b,3,"idle",2014-07-24 01:09:48 PDT,2/94,0,LOG,00000,"statement: select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb  where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname=':v_spcname' order by a.relpages desc limit 10;",,,,,,,,,"psql"


From the log we can see that the values of variable v_spcname did not pass to the sql.   any one know that ?
Using psql variables with quotes is a bit tricky.  As you noticed, they don't get substituted in certain positions, among other, between quotes.  

My only solution to this so far has been including the quote in the variable itself.  This is also not very straightforward:

test=# \set a 'parrot'
test=# \echo :a
parrot
test=# \set a ''parrot''
test=# \echo :a
parrot
test=# \set a '''parrot'''
test=# \echo :a
'parrot'
test=# \set a ''''parrot''''
test=# \echo :a
'parrot'


and so on.  This is, to my eyes, is basically the same as the quote duplication magic elsewhere (mentioned more than once in the documentation).

The solution I've posted in the comment is basically an ugly version of the three-quote version.

Note that dollar quoting does not work here:

test=# \set a $$'parrot'$$
test=# \echo :a
$$parrot$$
test=# \set a '$$parrot$$'
test=# \echo :a
$$parrot$$