Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 583
Dmitri Avatar asked Sep 14 '25 09:09

Dmitri


1 Answers

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.

like image 175
Abelisto Avatar answered Sep 17 '25 19:09

Abelisto