public inbox for [email protected]
help / color / mirror / Atom feedHow to pass arguments in postgres to sql scripts.
3+ messages / 3 participants
[nested] [flat]
* How to pass arguments in postgres to sql scripts.
@ 2024-04-27 13:03 kunwar singh <[email protected]>
2024-04-27 13:13 ` Re: How to pass arguments in postgres to sql scripts. Samed YILDIRIM <[email protected]>
2024-05-03 16:43 ` Re: How to pass arguments in postgres to sql scripts. Feike Steenbergen <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: kunwar singh @ 2024-04-27 13:03 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to pass arguments in postgres to sql scripts.
2024-04-27 13:03 How to pass arguments in postgres to sql scripts. kunwar singh <[email protected]>
@ 2024-04-27 13:13 ` Samed YILDIRIM <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Samed YILDIRIM @ 2024-04-27 13:13 UTC (permalink / raw)
To: kunwar singh <[email protected]>; +Cc: [email protected]
Hi Kunwar,
You can check -v option of psql, 4th paragraph and \set command in
"Meta-Commads" section, and "Variables" subsection under "Advanced
Features".
https://www.postgresql.org/docs/16/app-psql.html
https://www.postgresql.org/docs/16/app-psql.html#APP-PSQL-VARIABLES
Best regards.
Samed YILDIRIM
On Sat, 27 Apr 2024, 16:03 kunwar singh, <[email protected]> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to pass arguments in postgres to sql scripts.
2024-04-27 13:03 How to pass arguments in postgres to sql scripts. kunwar singh <[email protected]>
@ 2024-05-03 16:43 ` Feike Steenbergen <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Feike Steenbergen @ 2024-05-03 16:43 UTC (permalink / raw)
To: kunwar singh <[email protected]>; +Cc: [email protected]
On Sat, 27 Apr 2024 at 15:03, kunwar singh <[email protected]> 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.
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-05-03 16:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-27 13:03 How to pass arguments in postgres to sql scripts. kunwar singh <[email protected]>
2024-04-27 13:13 ` Samed YILDIRIM <[email protected]>
2024-05-03 16:43 ` Feike Steenbergen <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox