How to Get Current Database and User Name with 'select' in Postgresql

How to get current database and user name with `SELECT` in PostgreSQL?

By using the inbuilt System Information Functions

1.) Currently using database

   select current_database()

2.) Connected User

  select user

To get the desired output use either this

 select 'Database : ' ||current_database()||', '||'User : '|| user db_details

or

select format('Database: %s, User: %s',current_database(),user) db_details

Live Demo

How to check connected user on psql

To get information about current connection from the psql command prompt:

\conninfo

This displays more informations, though.

To change user:

\c - a_new_user

‘-’ substitutes for the current database.

To change database and user:

\c a_new_database a_new_user

The SQL command to get this information:

SELECT current_user;

---

Examples:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"

postgres=# \c a_new_database a_new_user
psql (12.1 (Ubuntu 12.1-1.pgdg16.04+1), server 9.5.20)
You are now connected to database "a_new_database" as user "a_new_user".

a_new_database=# SELECT current_user;
 current_user 
--------------
 a_new_user
(1 row)

---

This page list few interesting functions and variables.

https://www.postgresql.org/docs/current/static/functions-info.html

Get DB owner's name in PostgreSql

You can find such things in the system catalog

SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'database_name'
ORDER BY 1;

How can you get the active users connected to a postgreSQL database via SQL?

(question) Don't you get that info in

select * from pg_user;

or using the view pg_stat_activity:

select * from pg_stat_activity;

Added:

the view says:

One row per server process, showing database OID, database name, process ID, user OID, user name, current query, query's waiting status, time at which the current query began execution, time at which the process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off. Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on.

can't you filter and get that information? that will be the current users on the Database, you can use began execution time to get all queries from last 5 minutes for example...

something like that.

How to get data from table if username and password correct or return only text?

Based on your requirement. Please give the below code a try.

CREATE OR REPLACE FUNCTION check_login (username varchar, pasword varchar)
RETURNS "pg_catalog"."json" AS $BODY$
as $$
declare

    user_cursor refcursor;
    user_row record;

BEGIN

    open user_cursor for select * from FIELD_USER where user_name = username and password = pasword;;
    FETCH user_cursor INTO user_row;
    IF FOUND THEN
        return row_to_json(user_row);
    ELSE
         return '{"msg":"Email or Password is invalid"}';
    END IF;

END;$BODY$
LANGUAGE plpgsql;

Please note: A postgresql function can only return value of a single type. You can not return value of different types based on conditions.

use database_name command in PostgreSQL

When you get a connection to PostgreSQL it is always to a particular database. To access a different database, you must get a new connection.

Using \c in psql closes the old connection and acquires a new one, using the specified database and/or credentials. You get a whole new back-end process and everything.

postgres: What is the query 'select * from user' actually doing?

In this context, user is a reserved internal Postgres function that represents the current user logged in to the database.

This query can also be written as:

SELECT user;

Which should yield the same thing. Note, if you want to actually reference or create a table named user you'll have to use quotes, or fully qualify the schema it lives in. For example:

CREATE TABLE "user"
(
  id int2 not null
);

will work but:

CREATE TABLE user
(
  id int2 not null
);

Will yield an error.

Here's a reference for other system information functions:

http://www.postgresql.org/docs/9.0/static/functions-info.html

How to list active connections on PostgreSQL?

Oh, I just found that command on PostgreSQL forum:

SELECT * FROM pg_stat_activity;

How to get DB username in PL/Python function

You should use Database Access Functions. Note, that you have to be connected to a database to get the current user, so the argument of the function makes a little sense.

create or replace function py_current_user()
    returns text
    language plpython3u
as $$
    res = plpy.execute("select current_user")
    return res[0]["current_user"]
$$;

---

Related Topics