Using Substitution Variables In PostgreSQL

On 16/10/2017 17:08, Osahon Oduware wrote:

Hi All,

I wanted to find out how to use a substitution variable in an SQL statement that would cause the user to be prompted for a value. Something similar to the ampersand (&&) in ORACLE.

For example, given the SQL statement below:
    SELECT <col1>,<col2>,<col3>
    FROM <table>
    WHERE <col5> = 35

I want the user to be prompted for the value in the WHERE (filter) clause, e.g.
    SELECT <col1>,<col2>,<col3>
    FROM <table>
    WHERE <col5> = ?

I would be glad if someone could point me in the right direction.

Just write a bash script that asks for values and then use the -v feature of psql .

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Oct 16, 2017 at 7:08 AM, Osahon Oduware <osahon.gis@gmail.com>
wrote:

Hi All,

I wanted to find out how to use a substitution variable in an SQL
statement that would cause the user to be prompted for a value. Something
similar to the ampersand (&&) in ORACLE.

For example, given the SQL statement below:
SELECT <col1>,<col2>,<col3>
FROM <table>
WHERE <col5> = 35

I want the user to be prompted for the value in the WHERE (filter) clause,
e.g.
SELECT <col1>,<col2>,<col3>
FROM <table>
WHERE <col5> = ?

I would be glad if someone could point me in the right direction.

​That would be a client-side feature. The only client supported on this
list is psql. psql does not have this capability. It does have the
"\prompt" ​meta-command which will serve in this specific case.

\prompt 'Enter a value for varname' varname

SELECT ... WHERE <col5> = :'varname';

https://www.postgresql.org/docs/10/static/app-psql.html

David J.

Thanks for the information.

On Mon, Oct 16, 2017 at 3:27 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote: