Reputation: 59
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
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
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