How populate : variables dynamically?

From: Doug <postgres(dot)dba(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: How populate : variables dynamically?
Date: 2005-12-15 14:09:24
Message-ID: D5C8418D-AD2D-40B1-B51D-C39BE8868F4D@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I can't find a way to do something in PostgreSQL that I am used to in
Oracle:
How can I populate a variable in psql that can be used in subsequent
places in the same session, by SELECTing a value?

Here's an example in Oracle:
SET VERIFY OFF LINESIZE 132
COLUMN OSID NEW_VALUE ORACLE_SID NOPRINT;
COLUMN SCHEMA_VALUE NEW_VALUE SCHEMA NOPRINT;
COLUMN INSTANCEVERSION NEW_VALUE ORACLE_VERSION NOPRINT;
-- COLUMN MON_YY NEW_VALUE MONYY NOPRINT;
SELECT SYS_CONTEXT('USERENV','DB_NAME') AS OSID FROM DUAL;
SELECT USER AS SCHEMA_VALUE FROM DUAL;
SELECT VERSION AS INSTANCEVERSION FROM V
$INSTANCE;
DEFINE OWNER=&&SCHEMA

PROMPT ORACLE_SID IS &&ORACLE_SID
PROMPT SCHEMA IS &&SCHEMA
PROMPT ORACLE_VERSION IS &&ORACLE_VERSION

SELECT '&&ORACLE_SID' AS ORASID, '&&ORACLE_VERSION' AS ORAVER,
'&&SCHEMA' AS CURRUSER FROM DUAL;

Here's the output from running it:
ORACLE_SID IS NC9I
SCHEMA IS DBMAINT
ORACLE_VERSION IS 9.2.0.6.0

ORASID ORAVER
CURRUSER
-------------------------------- --------------------------------
--------------------------------
NC9I 9.2.0.6.0
DBMAINT

The point is to populate session variables dynamically using a select
statement
and then be able to use them more than once during the same session.
Some of these could be put in the Oracle login.sql so the values are
always available.

What I can not find is how to populate ':' variables in PostgreSQL
like this. Is this supported, and I just haven't found the
documentation
that describes how to do it?

Thanks for your help.
--doug

Browse pgsql-novice by date

  From Date Subject
Next Message T. Lackey 2005-12-15 14:12:40 Command line psql and other ODBC access.
Previous Message Rahul Patil 2005-12-15 12:56:30 can not create trigger with parameter