How to store the select statement output into variable thro PSQL ( postgresql )

I am exploring postgresql database on Amazon cloud environment ( Redshift ). I would like to store the value into variable using \set command as follows. But I am not able to achieve that.

I would like to store the output of "current_date + 90" into one variable and set it to USER while create the user account:

postgres=# select current_date;
    date
------------
 2014-01-31
(1 row)

postgres=#
postgres=# select current_date+90;
  ?column?
------------
 2014-05-01
(1 row)

I would like to rest above date as below:

alter user <user_name> with password <new_passowrd> VALID UNTIL 'current_date+90';

---below is my reply to the person who replied to me ( he has aked me to use DO $$ ..)

Hi,

Thank you for your time and reply to my thread.

I am calling .sql file in psql prompt as follows:

psql -h <host_name> -U <user_name> -d <db_name> -p <port> -a -f c:\redshift\scripts\psql-alter-user.sql --log-file c:\testlogs\alter-user.log -v v_username=test_user3 -v v_password='Today123#'

(ii) below is the script content ( psql-alter-user.sql )

\echo :v_username
\echo :v_password

\set v_date 'select current_timestamp+90;'
:v_date

alter user :v_username with password :v_password valid until ':v_date';

\q

(iii) further after executing this script, below is my log file:

********* QUERY **********
select current_timestamp+90;
**************************

           ?column?            
-------------------------------
 2014-05-01 23:40:47.856804+00
(1 row)

********* QUERY **********
alter user test_user3 with password 'Today123#' valid until ':v_date';
**************************

I am not able to bring the actual value ( 2014-05-01 23:40:47.856804+00 ) as :v_date in the ALTER USER syntax. could you please help me. This will help me a lot in my current project.

Thank you Keavan

Answers


You can't do this purely within Redshift, because the only way to do it in PostgreSQL is to use dynamic SQL - EXECUTE within a PL/PgSQL function or DO block. You don't have PL/pgSQL in Redshift.

What you must do instead is have the client-side script read the value of current_timestamp+90, store that in a variable, and generate the ALTER statement it sends to the server from the result.

If you're going to do that, why not just let the client side generate the expiry time to start with, rather than asking the server? If you're using a shell script, for example:

password="fr'ed"
expires=`date -I -d '+ 90 days'`
psql -c "alter user test_user3 with password '${password//\'/''}' valid until '${expires} 00:00:00';"

Note the doubling of single-quotes in $password.


One ugly trick is possible (clean solution is available from PostgreSQL 9.3)

postgres=# \set myvar `psql -At postgres -c "select current_date+90;"`
postgres=# \set user tom
postgres=# \set password somepassword

postgres=# create role tom login;
CREATE ROLE
Time: 45.915 ms
postgres=# alter user :user with password :'password' valid until :'myvar';
ALTER ROLE
Time: 8.599 ms

postgres=# \du tom
                            List of roles
 Role name │                 Attributes                  │ Member of 
───────────┼─────────────────────────────────────────────┼───────────
 tom       │ Password valid until 2014-05-02 00:00:00+02 │ {}

Now I am not sure, if safe escaped psql vars are available in Redshift, because it is relative old fork of Postgres. Craig Ringer solution is probably only one and correct.


Recent versions of PSQL have the /gset meta-command which handles this well.

Postgres current version psql documentation

\set :v_username tom
\echo :v_username
-- tom

-- notice the lack of a ';'
select current_date + 90 v_date  \gset 

\echo :v_date
-- 2019-05-22

alter user :user valid until :'v_date';

I find this extremely convenient for lightweight procedures stored as variables in my .psqlrc file.

Note that although the date as a string works in the alter user statement, usage in more generic queries with date functions often require an explicit cast to ::date, for example:

select date_trunc('year',:v_date);
-- ERROR:  function date_trunc("unknown", integer) does not exist

select date_trunc('year',:'v_date'::date);
┌─────────────────────┐
│     date_trunc      │
├─────────────────────┤
│ 2019-01-01 00:00:00 │
└─────────────────────┘

Need Your Help

PHP function to convert HSL to RGB or Hex

php css css3 hsl

Does anyone know a PHP function (for >5.3) which can convert an HSL color to either RGB or Hex? I've tried a dozen Google searches and none of the functions I have found work as expected.

Disable Entity Framework DateTime Default Value

c# winforms entity-framework-6

In my .edmx my date field has DefaultValue set to (None) but when i create a new record 01/01/0001 value appear . Also in my SQL , there is no default value . How can disable it ????