0

I have an Issue running a psql script.

this is the output :

File.SQL:155: ERROR: syntax error at or near ":" LINE 10: date_temp_Ale INTEGER := :'param_date';

i have a file.sh that is calling the "File.sql" with and Input varible. here is the line that callsthe sql script:

line in file.sh:

echo le numero des parametres est bien 7 
psql -v PGPASSWORD=$MDP -h $IP_MACHINE -f $FIC_REQ_SQL -v param_date=20180101  -U  $UTILISATEUR -d $NOM_BDD -p $PORT > $FIC_LOG_EXEC_TMP 2>&1  


echo Done in $FIC_LOG_EXEC_TMP

here is the line in the psql script: line in file.sql"

--- UT 48  PURGE DES TRACES
\set ON_ERROR_STOP on

BEGIN; -- DEBUT DE TRANSACTION

 DO $$ 
DECLARE
  num_tra_err_id RECORD;
  num_trace_composant RECORD;
  num_trace_fonctionnelle RECORD;
  num_message_id RECORD;
  num_metadonnees_id RECORD;
  num_Process_id RECORD;
  datediff integer;
  date_temp_Ale INTEGER := :'param_date';      

  Max_days  integer := 180;
  --date_limite varchar := &1;
BEGIN

i also tried to use the command " --set " in the file.sh but it didn't work.

Ale
  • 31
  • 4
  • I'm guessing you probably don't want those single quotes around `param_date` on that line. – Shawn Feb 19 '19 at 16:34
  • hi Shawn, thans for the reply, I tried both with single quote and also without but the error persist. – Ale Feb 20 '19 at 10:29
  • i figured out that if i retrieve the variable before the DO $$, then I can correctly access it, but if i do the same inside the the DO $$ then I get the error ! now i am still facing the issue of how to use the variable (which i retrieved before the DO $$), inside the DO $$ – Ale Feb 20 '19 at 15:42
  • what i did it is : SELECT :param_date::date into temp_val; but now I cannot use the variable temp_val inside the DO $$ because i get an error "relation "temp_val" already exists" – Ale Feb 20 '19 at 15:46

1 Answers1

0

i found a slution:

the DO statement isolate the inner code from the outer code.

to pass the info we need to store it as psql variable as follow

select set_config('psql.param_date', :param_date, false);

BEGIN; -- DEBUT DE TRANSACTION

  DO 

  $$ 

    DECLARE

      limit_date varchar = current_setting('psql.param_date');
Ale
  • 31
  • 4