Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

--tablespace

skypcsuit=> \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=> \set v_spcname tbs_skypcsuit_idx

skypcsuit=> \echo :v_spcname 
tbs_skypcsuit_idx

skypcsuit=> :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 ?

share|improve this question
    
try \set v_scpname '\'tbs_skypcsuit_idx\'' –  dezso Jul 24 at 9:22
    
@dezso : I test your advice , it works fine, thanks! –  francs Jul 25 at 2:08

1 Answer 1

up vote 1 down vote accepted

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$$
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.