How to append strings in PL/pgSQL?

Question

I have a procedure that queries schema and table. I want this to not overwrite the variable instead append, because the result I want to create view.

DECLARE    
    rec record;
    sqlPlaceholder text;
BEGIN
 
 FOR rec IN SELECT table_schema, table_name
            FROM information_schema.tables                
 LOOP
     sqlPlaceholder := sqlPlaceholder || 'SELECT ogc_fid, wkb_geometry FROM ' || 
               rec.table_schema || '.' || rec.table_name || ' UNION ';
 END LOOP;
  
strSQL := 'SELECT  * FROM (' || sqlPlaceholder || ')';

RAISE INFO 'sample output %' , strSQL;

The variable sqlPlaceholder only holds the last assignment from the loop. as it's overwritten by each loop.

I need to figure out how add them all in loop.

call create_view()

Value of string inside loop SELECT *  FROM public._data_202101 UNION 
Value of string inside loop SELECT *  FROM public._data_202102 UNION 
Value of string inside loop SELECT *  FROM public._data_202103 UNION 
Value of string inside loop SELECT *  FROM public._data_202104 UNION 
Value of string inside loop SELECT *  FROM public._data_202105 UNION 
Value of string inside loop SELECT *  FROM public._data_202106 UNION 
Value of string inside loop SELECT *  FROM public._data_202107 UNION 
Value of string inside loop SELECT *  FROM public._data_202108 UNION 
Value of string inside loop SELECT *  FROM public._data_202109 UNION 
Value of string inside loop SELECT *  FROM public._data_202110 UNION 
Value of string inside loop SELECT *  FROM public._data_202111 UNION 
Value of string inside loop SELECT *  FROM public._data_202112 UNION 
Value of string outside ==> create table public._data_all as (SELECT *  FROM public._data_202112 union)

Are you sure? You could add a debug line (`RAISE INFO`) in the loop to see the value of `sqlPlaceholder` during each loop execution. — John Rotenstein, Jan 27 '22 at 23:34
Note: if the loop executes N times you'll need N-1 `' UNION '` s. — wildplasser, Jan 27 '22 at 23:46
yes, I did added inside the loop also, here is the output of that, added in answer — hi4ppl, Jan 27 '22 at 23:47

Erwin Brandstetter · Answer 1 · 2022-01-28T01:08:17.187

3

The core problem in your PL/pgSQL code is that sqlPlaceholder starts out as NULL. Read the manual here.

NULL || anything is always NULL. Initialize the variable with an empty string ('') instead to fix that:

DECLARE    
    sqlPlaceholder text := '';
...

There's more, but don't bother. Here is a superior set-based solution:

SELECT string_agg(format('SELECT ogc_fid, wkb_geometry FROM %I.%I', table_schema, table_name), E'\nUNION ')
FROM   information_schema.tables;

Redshift is pretty limited, I don't think it has format(). So:

SELECT string_agg('SELECT ogc_fid, wkb_geometry FROM ' || quote_ident(table_schema) || '.' || quote_ident(table_name), '\nUNION ')
FROM   information_schema.tables;

Produces something like:

SELECT ogc_fid, wkb_geometry FROM public.tbl1
UNION SELECT ogc_fid, wkb_geometry FROM public.tbl2
UNION SELECT ogc_fid, wkb_geometry FROM public.tbl3
...

You want to quote identifiers properly to defend against SQL injection in any case! See:

Also, you probably want some filter and UNION ALL instead of UNION.

answered Jan 27 '22 at 23:54

Erwin Brandstetter

605,456
145
1,078
1,228

i'm getting this error Schema Error: error: unterminated quoted string at or near "E'\nUNION ) FROM information_schema.tables;" – hi4ppl Jan 27 '22 at 23:59
The missing single quote is already fixed. – Erwin Brandstetter Jan 28 '22 at 00:00
on the new one I get some other issue, maybe it's not supported [42883] ERROR: function format("unknown", information_schema.sql_identifier, information_schema.sql_identifier) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts. – hi4ppl Jan 28 '22 at 00:10
I already addressed that, too, by now. – Erwin Brandstetter Jan 28 '22 at 00:11
looks like e is not supported in redshift either. [42704] ERROR: type "e" does not exist – hi4ppl Jan 28 '22 at 00:31
Yeah, Redshift is stuck in the past where Postgres has long since evolved. See: https://stackoverflow.com/a/12320729/939860 – Erwin Brandstetter Jan 28 '22 at 01:09
@hi4ppl: this is precisely the reason you shouldn't tag Redshift questions with Postgres. – Jan 28 '22 at 06:34
@a_horse_with_no_name the only reason I tagged was that the same procedure have same result both in redshift and PostgreSQL so I needed that to work in both, I appreciate the insight and help, but my question was why the variable overwritten on each loop and how to append in variable. – hi4ppl Jan 28 '22 at 12:57
@hi4ppl: I added an explanation for the original question, too. (In case you missed it.) – Erwin Brandstetter Jan 28 '22 at 14:43
So is this answered properly? – Erwin Brandstetter Feb 17 '22 at 02:49

How to append strings in PL/pgSQL?

1 Answers1