CODEWITHSUNDEEP
sqldatabasepostgresqlplpgsqlpostgresql-9.3
Tarcisio M.
Tarcisio M.

Reputation: 59

Error while concatenating plpgsql var with query on cursor statement

I am getting error trying concatenate the var sch in the second For:

ERROR: syntax error in or next a "||" SQL state: 42601 Character: 1151

Does anyone know how to solve this problem concatenation?

CREATE OR REPLACE FUNCTION generate_mallet_input2() RETURNS VOID AS $$ 
DECLARE 
sch name;
r record;   
BEGIN

    FOR sch IN 
     select schema_name from information_schema.schemata where schema_name not in ('test','summary','public','pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','information_schema') 
    LOOP 
         FOR r IN SELECT rp.id as id,g.classified as classif, concat(rp.summary,rp.description,string_agg(c.message, '. ')) as mess
            FROM  sch.report rp
            INNER JOIN || sch || .report_comment rc ON rp.id=rc.report_id
            INNER JOIN || sch || .comment c ON rc.comments_generatedid=c.generatedid 
            INNER JOIN || sch || .gold_set g ON rp.id=g.key
            WHERE g.classified = any (values('BUG'),('IMPROVEMENT'),('REFACTORING'))
            GROUP BY g.classified,rp.summary,rp.description,rp.id
         LOOP

            IF r.classif = 'BUG' THEN
                EXECUTE 'Copy( Values('|| r.mess  || ') ) To ''/tmp/csv-temp/BUG/' || quote_ident(sch) || '-' || r.id || '.txt '' With DELIMITER '' '' ';
            ELSIF r.classif = 'IMPROVEMENT' THEN
                EXECUTE 'Copy( Values('|| r.mess  || ') ) To ''/tmp/csv-temp/IMPROVEMENT/' || quote_ident(sch) || '-' || r.id || '.txt '' With DELIMITER '' '' ';
            ELSIF r.classif = 'REFACTORING' THEN
                EXECUTE 'Copy( Values('|| r.mess  || ') ) To ''/tmp/csv-temp/REFACTORING/' || quote_ident(sch) || '-' || r.id || '.txt '' With DELIMITER '' '' ';
            END IF;             

         END LOOP;



    END LOOP; 
    RETURN; 

END;
$$ LANGUAGE plpgsql STRICT;

Upvotes: 0

Views: 147

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45950

You cannot to dynamically change schema for embedded SQL. Any database related identifier have to be constant. If you have some modern PostgreSQL (probably higher than 9.2, works in 9.4), you can change search path:

create schema s1;
create schema s2;
create table s1.t1(a int);
create table s2.t1(a int);
insert into s1.t1 values(10);
insert into s2.t1 values(20);

do $$
declare r record;
begin
  for  i in 1..2 loop
    perform set_config('search_path', 's'||i, true);
    for r in select * from t1 loop 
      raise notice '%', r;
    end loop;
  end loop; 
end; $$;

If this code doesn't work, you have to use dynamic SQL - FOR IN EXECUTE statement.

Upvotes: 1

miracle_the_V
miracle_the_V

Reputation: 1166

Not sure, have no PostgreSQL installed now, but it looks like you have to do something like

 INNER JOIN || sch || '.report_comment' rc ON rp.id=rc.report_id

It treats .report_comment part as a variable or something the other way.

And if the whole script has to be generated dynamically, depending on sch value

FOR r IN SELECT rp.id as id,g.classified as classif, concat(rp.summary,rp.description,string_agg(c.message, '. ')) as mess
            FROM  sch.report rp
            INNER JOIN || sch || .report_comment rc ON rp.id=rc.report_id
            INNER JOIN || sch || .comment c ON rc.comments_generatedid=c.generatedid 
            INNER JOIN || sch || .gold_set g ON rp.id=g.key
            WHERE g.classified = any (values('BUG'),('IMPROVEMENT'),('REFACTORING'))
            GROUP BY g.classified,rp.summary,rp.description,rp.id

then you should call it dynamic way - execute blabla, just like you are doing in the code above.

Upvotes: 0

Related Questions