Re: How to pass arguments in postgres to sql scripts compared when moving to Oracle sql

  Kunwar,    

 You could explore using psql to invoke sql  

scripts and pass parameters..    

Hopefully, the following link would be a starting  

point and help in answering your question..    

 postgresql.org/docs/current/app-psql.html/#APP-PSQL-INTERPOLATION    

Best,  

Rajeev      

>
> On Apr 27, 2024 at 9:08 AM, <kunwar singh (mailto:krishsingh.111_at_gmail.com)> wrote:
>
>
>
> Hello Listers,
> Hope everyone is doing well.
>
>
>
>
> Question: How to pass arguments in postgres to sql scripts.
>
> Details: 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> _at_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 the script 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

--
http://www.freelists.org/webpage/oracle-l