sqlconcat two variables postgresql

Question

Im writing a psql function. I want to do a concat between two variables inside psql. and im getting syntax_error.

Let's notice that date_contract is of type date.

Thank you Who can help me

declare
result_table regclass := $$public.contract$$||text;
time_now time
BEGIN
   execute $$SELECT MIN(date_contract) FROM $$||result_table INTO date_;
   execute  $$SELECT CURRENT_TIMESTAMP::time FROM $$||result_table INTO time_now;
 execute $$
INSERT INTO $$||result_table||$$
(id, dat_beg_contract, dat_end_contract,date_contract, long_c)

   SELECT id, dat_beg_contract, dat_end_contract, sum(extract(epoch from (least(s.dat_beg_contract, gs.date_contract||time_ + interval '1 day')::timestamp -
                           greatest(s.dat_beg_contract, gs.date_contract)
                          )
              ) / 60) as long_c 

   $$;
   END;

@JimJones thank you but got this error psycopg2.ProgrammingError: column "time_now" does not exist — dsp, Feb 02 '21 at 14:17
it's because `time_now` does not exist in your table :) I will add an example as an aswer for you to take a look — Jim Jones, Feb 02 '21 at 14:19
btw: in your code there is no indication that the table `tab` exists! I will add two examples with anonymous code block and function — Jim Jones, Feb 02 '21 at 14:44

Jim Jones · Answer 1 · 2021-02-02T15:30:14.587

This code block might give you an idea of how to concatenate variables

DO $$
DECLARE
rec1 text;rec2 text;
BEGIN
  EXECUTE 'SELECT ''foo'' ' INTO rec1;     
  EXECUTE 'SELECT ''bar'' 'INTO rec2; 
  RAISE NOTICE 'Option 1 %, Opiton 2: %', rec1||rec2, rec1||' '||rec2 ;
END; $$ LANGUAGE plpgsql;

NOTICE:  Option 1 foobar, Opiton 2: foo bar

EDIT Returning a query with a function

CREATE TABLE tab (id int);
INSERT INTO tab VALUES (100),(42);

CREATE OR REPLACE FUNCTION myfunc (table_name TEXT)
RETURNS TABLE (res int) AS $$
DECLARE v1 int; v2 int;
BEGIN
  EXECUTE 'SELECT max(id) FROM '||table_name INTO v1;     
  EXECUTE 'SELECT min(id) FROM '||table_name INTO v2;     
 RETURN QUERY EXECUTE 'SELECT ' || v1 || v2 ;

END;
$$ LANGUAGE plpgsql;


SELECT myfunc('tab');
 myfunc 
--------
  10042

EDIT 2 Example concatenating timestamp and integer

CREATE TABLE tab (id int);
INSERT INTO tab VALUES (100),(42);

CREATE OR REPLACE FUNCTION myfunc (table_name TEXT)
RETURNS TABLE (res text) AS $$
DECLARE v1 int; v2 timestamp;
BEGIN
  EXECUTE 'SELECT min(id) FROM '||table_name INTO v1;     
  EXECUTE 'SELECT CURRENT_TIMESTAMP' INTO v2;     
 RETURN QUERY EXECUTE 'SELECT ' || quote_literal(v1 || ' - ' ||v2) ;

END;
$$ LANGUAGE plpgsql;

SELECT myfunc('tab');
  EXECUTE 'SELECT min(id) FROM '||table_name INTO v1;     
  EXECUTE 'SELECT CURRENT_TIMESTAMP' INTO v2;     
 RETURN QUERY EXECUTE 'SELECT ' || quote_literal(v1::text || ' - ' ||v2) ;

END;
$$ LANGUAGE plpgsql;

SELECT myfunc('tab');

             myfunc             
--------------------------------
 42 - 2021-02-02 15:54:24.24179
(1 Zeile)

EDIT 3 let me know if this works, so that I can clean the answer

CREATE TABLE tab (date_contract date);
INSERT INTO tab VALUES (current_date+7),(current_date);

CREATE OR REPLACE FUNCTION myfunc (table_name TEXT)
RETURNS TABLE (date_contract date, col_new text) AS $$
DECLARE date_ date;
BEGIN
  EXECUTE 'SELECT min(date_contract) FROM '||table_name INTO date_;     
 RETURN QUERY EXECUTE 'SELECT date_contract,'|| quote_literal(date_|| ' ' ||current_time)  ||' FROM ' || table_name;

END;
$$ LANGUAGE plpgsql;

SELECT * FROM myfunc('tab');

 date_contract |            col_new            
---------------+-------------------------------
 2021-02-09    | 2021-02-02 16:29:49.013344+01
 2021-02-02    | 2021-02-02 16:29:49.013344+01
(2 Zeilen)

thank you Jim, but i didn't got you ! :/ what is wrong in what i wrote please? (original pub) — dsp, Feb 02 '21 at 14:26
it's really weird i didn't succeed to resolve the error !! @Jim Jones — dsp, Feb 02 '21 at 15:09
@dsp consider adding the following to your question: 1) create table statements to the tables involved, 2) insert statements with some sample data, 3) a brief description of your use case and 4) the exact expected result :) Doing so I believe we can find a solution for you. — Jim Jones, Feb 02 '21 at 15:11
Thank you !! Give me just 5 minutes. I will put more details. Danke again — dsp, Feb 02 '21 at 15:14
@dsp I just added another try to my answer. I believe you're now close ;) — Jim Jones, Feb 02 '21 at 15:30
@dsp have you tried my last two edits? consider adding the following to your question: 1) create table statements to the tables involved, 2) insert statements with some sample data, 3) a brief description of your use case and 4) the exact expected result. — Jim Jones, Feb 03 '21 at 14:19

sqlconcat two variables postgresql

1 Answers1