PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to make an alias for a common query to run on any database?

I often find myself wanting to run a query like this on multiple different databases:

select n, pg_size_pretty(s) from (
  select n, pg_total_relation_size(n) s from (
    select format('%I.%I', table_schema, table_name) n from information_schema.tables
  )
)
order by s desc;

How can I make an alias for this query so that I can psql into any database and run it easily?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found out I can put an alias in my ~/.psqlrc:

    set tablesizes 'select n, pg_size_pretty(s) from ( select n, pg_total_relation_size(n) s from ( select format(''%I.%I'', table_schema, table_name) n from information_schema.tables)) order by s desc;'

    Then I can use it like this:

    $ psql $DB_URL
postgres=# :tablesizes
┌───────────────────────────────────────────────────────────────────────┬────────────────┐
│                                   n                                   │ pg_size_pretty │
├───────────────────────────────────────────────────────────────────────┼────────────────┤
│ pg_catalog.pg_proc                                                    │ 1448 kB        │
│ pg_catalog.pg_attribute                                               │ 848 kB         │
│ pg_catalog.pg_rewrite                                                 │ 824 kB         │
│ pg_catalog.pg_description                                             │ 616 kB         │
│ pg_catalog.pg_depend                                                  │ 448 kB         │
│ pg_catalog.pg_statistic                                               │ 336 kB         │

  2. 0

    Use CREATE VIEW. It is basically a query alias that is executed every time you access it.

    
-- create
CREATE TABLE employee (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

-- insert
INSERT INTO employee VALUES (0001, 'Clark', 'Sales');
INSERT INTO employee VALUES (0002, 'Dave', 'Accounting');
INSERT INTO employee VALUES (0003, 'Ava', 'Sales');


CREATE VIEW odd_employee AS (
  --- Wrote repeated query here.
  SELECT empId, name
  FROM employee
  WHERE empId % 2 = 1
);

SELECT * FROM odd_employee;

INSERT INTO employee VALUES (0005, 'Alla', 'IT');

SELECT * FROM odd_employee;

    Output:

    CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE VIEW
 empid | name  
-------+-------
     1 | Clark
     3 | Ava
(2 rows)

INSERT 0 1
 empid | name  
-------+-------
     1 | Clark
     3 | Ava
     5 | Alla
(3 rows)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search