Alias for a complete SQL statement? (in PostgreSQL psql)

Question

In PostgreSQL 9.5 psql (for use within an interactive session), I wold like to create an alias to a complete SQL statement, analogous to a shell alias. The objective is to just get the output printed on the screen.

If I could enable formatted server output (in Oracle terms) from within a stored procedure, it would look like this:

CREATE or replace FUNCTION print_my_table()
RETURNS void
AS $$
    -- somehow enable output here
    SELECT * from my_table;
$$ LANGUAGE SQL;

This would be invoked as print_my_table(); (as opposed to SELECT x FROM ...)

I know I can use 'RAISE NOTICE' to print from within a stored procedure, but to do that I would need to reimplement pretty-printing of a table.

Perhaps there is a completely different mechanism to do this?

(my_table stands for a complex SQL statement that collects server data accounting information, or a my_table() stored procedure returning a table)

EDIT

The solution provided by @Abelisto (using psql variables) enables the creation of aliases to arbitrary statements, beyond merely printing the result to the screen.

Maybe I am not fully understanding, but why not just make a view? — Nick
– Nick, Commented Jul 22, 2016 at 22:49
With a view I will still have to say select * from <view>; what I want is to just say do_it() and get the results printed. — Dmitri
– Dmitri, Commented Jul 22, 2016 at 22:50
This needs to be done within psql or would it be acceptable to alias psql -c "SELECT * FROM mytable;" ? — Nick
– Nick, Commented Jul 22, 2016 at 22:52
How are you planning on using this? Why do you need to do this? — Nick
– Nick, Commented Jul 22, 2016 at 23:11

Abelisto · Accepted Answer · 2016-07-22 23:49:11Z

6

There is so called internal variables in the psql utility which will be replaced by its content (except inside the string constants):

postgres=# \set foo 'select 1;'
postgres=# :foo
 ?column? 
----------
        1
(1 row)

It can be also set by the command line option -v:

psql -v foo='select 1;' -v bar='select 2;'

Create the text file like

\set foo 'select 1;'
\set bar 'select 2;'
\set stringinside 'select $$abc$$;'

and load it using \i command.

Finally you can create the file ~/.psqlrc (its purpose is like ~/.bashrc file) and its content will be automatically executed each time when psql starts.

answered Jul 22, 2016 at 23:49

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dmitri Over a year ago

Critical insight here is that an internal variable can contain a statement (which will be executed when the variable is invoked). The variables even get tab-expanded, like in (reasonably modern) Unix shells. So in this usage, they provide a close analog to shell aliases.

baxx Over a year ago

What if the command is longer than a few words such as select 1; ? Is there a way to store the command in a file and source it from the set ? Something like \set foo source(path/to/command.sql)

Collectives™ on Stack Overflow

Alias for a complete SQL statement? (in PostgreSQL psql)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related