Hi friends, Question: How to pass arguments in postgres to sql scripts. Context. When I am monitoring my production Oracle databases I have a lot of simple sql scripts (which require one or more arguments) which make my monitoring/troubleshooting life simpler. How can I achieve the same in postgres? We are migrating our Oracle databases to Postgres and I am modifying my scripts to do the same in Postgres.
Oracle ====== cat appjobcheck.sql
SELECT SID, SYS_CONTEXT.GET_CURRENT_SCHEMA() AS "SCHEMA", -- Similar to datname USERNAME, PROGRAM AS "APPLICATION_NAME", STATUS, SQL_ID, -- Use V$SQL to get full query text based on SQL_ID LOGON_TIME AS "BACKEND_START", SQL_EXEC_START AS "QUERY_START", FROM V$SESSION WHERE STATUS = 'ACTIVE' -- Filter to active sessions AND TYPE != 'BACKGROUND'; -- Exclude background processes AND program='&1';
The way to invoke from sqlplus is .
SQL> @appjobcheck batchprocessapp1 ---- batchprocessapp1 is what I want to monitor
Postgres ========== cat appjobcheck.sql
SELECT pid, datname, usename, application_name, state, query, backend_start, query_start FROM pg_stat_activity where application_name='&1';
The way I am trying invoke from postgres is .
postgres=> \i appjobcheck.sql batchprocessapp1 ---- batchprocessapp1 is what I want to monitor pid | datname | usename | application_name | state | query | backend_start | query_start -----+---------+---------+------------------+-------+-------+---------------+------------- (0 rows)
