Thread: How to pass arguments in postgres to sql scripts.
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)
\i: extra argument "batchprocessapp1" ignored
======
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)
\i: extra argument "batchprocessapp1" ignored
Cheers,
Kunwar
Kunwar
Hi Kunwar,
You can check -v option of psql, 4th paragraph and \set command in "Meta-Commads" section, and "Variables" subsection under "Advanced Features".
Best regards.
Samed YILDIRIM
Samed YILDIRIM
On Sat, 27 Apr 2024, 16:03 kunwar singh, <krishsingh.111@gmail.com> wrote:
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)
\i: extra argument "batchprocessapp1" ignored--Cheers,
Kunwar
On Sat, 27 Apr 2024 at 15:03, kunwar singh <krishsingh.111@gmail.com> wrote: > > Hi friends, Question: How to pass arguments in postgres to sql scripts. Context. When I am monitoring my production Oracledatabases I have a lot of simple sql scripts (which require one or more arguments) which make my monitoring/troubleshootinglife simpler. How can I achieve the same in postgres? We are migrating our Oracle databases toPostgres and I am modifying my scripts to do the same in Postgres. One way to do it is with --set $ psql --set var1=postgres -AtXq postgres=# select pid from pg_stat_activity where usename=:'var1'; 185 1939 Kind regards, Feike Steenbergen