use concat function postgresql

Question

I want to run this code but got this error

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

execute $$SELECT MAX(date) FROM $$||table_name INTO datum;
IF datum is not NULL THEN

      execute $$DELETE FROM $$||table_name||$$ WHERE date >= $$||quote_literal(datum);
ELSE
      datum := $$2001-01-01$$;
end if;
execute $$
INSERT INTO $$||table_name||$$
(number, date, departement, minutes)
 select
     number, gs.date,

where (beg, enddate) overlaps (concat($$||quote_literal(datum)||$$, '00:00:00'), concat($$||quote_literal(datum)||$$, '23:59:59'))

Let's notice also that where (beg, enddate) overlaps (concat($$||quote_literal(datum)||$$, '00:00:00'), concat($$||quote_literal(datum)||$$, '23:59:59')) want to do it only when datum != $$2001-01-01$$; (not for the first iteration).

Thank you,

|| is already a concatenation operator, which concatenates. So, you won't need to use concat additionally. Read more here postgresql.org/docs/9.1/functions-string.html — kabirbaidhya
– kabirbaidhya, Commented Sep 24, 2020 at 13:05
i see i did this before $$||quote_literal(datum)||$$ '00:00:00' and got error also:::::::: psycopg2.ProgrammingError: syntax error at or near ""00:00:00"" — biwia
– biwia, Commented Sep 24, 2020 at 13:09

score 1 · Accepted Answer · 2020-09-24 14:49:38Z

1

When using dynamic SQL, it's better to use format() to construct the SQL and use placeholders for parameters so that you don't need to cast values back and forth:

execute format('SELECT MAX(date) FROM %I', table_name) 
   INTO datum;

IF datum is not NULL THEN
      execute format('DELETE FROM %I WHERE date >= $1', table_name)
          using datum;
ELSE
   datum := date '2001-01-01'; 
end if;

execute format('
INSERT INTO %I
(number, date, departement, minutes)
select
     number, gs.date,
      (case
            when trim(string) ~ '^ADM[0-9]$'
              or TRIM(string) ~ '^ADM[0-9]$'
       then 'ADMINISTRATION'
      end) as departement,
sum(extract(epoch from (least(s.enddate, gs.date + interval '1 day' -
                           greatest(s.beg, gs.date)
                          )
              ) / 60) as minutes
from smp s 
  cross join lateral generate_series(date_trunc('day', s.beg), date_trunc('day', least(s.enddate, current_date)), interval '1 day') gs(date)
where (beg, enddate) overlaps ($1 + time '00:00:00', $1 + time '23:59:59'))', table_name)
using datum; -- pass the parameter as a proper date

edited Sep 24, 2020 at 14:49

answered Sep 24, 2020 at 13:11

user330315

Sign up to request clarification or add additional context in comments.

9 Comments

biwia Over a year ago

thank you but im gotting error saying that psycopg2.ProgrammingError: column "datum" does not exist Notice that datum is a variable not a column. @a_horse_with_no_name

user330315 Over a year ago

@biwia: well your query uses a column datum and you told me the data type of that column is date. I can only use the information you provide. Or did you mean to reference the date column from generate_series()?

biwia Over a year ago

@a_horse_with_name sorry its my fault I created like this. Sorry i was not clear execute $$SELECT MIN(sign_in_date) FROM $$||table_name INTO datum;

user330315 Over a year ago

So that's part of a larger PL/pgSQL function? Then please edit your question and show us the complete function. The whole query doesn't really make sense to me to begin with. And why the $$ quoting around the select that makes no sense either

biwia Over a year ago

thank you, post edited. Please have a look @a_horse_with_no_name

|

Collectives™ on Stack Overflow

use concat function postgresql

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related