Thread

1. How to pass arguments in postgres to sql scripts.

   kunwar singh <krishsingh.111@gmail.com> — 2024-04-27T13:03:16Z

   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
   

2. Re: How to pass arguments in postgres to sql scripts.

   Samed YILDIRIM <samed@reddoc.net> — 2024-04-27T13:13:50Z

   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, <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
   >
   

3. Re: How to pass arguments in postgres to sql scripts.

   Feike Steenbergen <feikesteenbergen@gmail.com> — 2024-05-03T16:43:42Z

   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 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