SQL Error[42601]: Error: syntax error at or near "0"

Question

I can't understand how ti make an array from select and I have an error

SQL Error[42601]: Error: syntax error at or near "0"", Error occurred on dblink connection named "unnamed": could not execute query.

create temporary table house_address as
   (SELECT full_address
    FROM dblink('db_d',
         'drop table if exists _x17092018;
             create temporary table _x17092018 (
             guid character varying,
             full_address character varying,
             address_guid character varying
          ); 
          do $$
             declare
                guids_list character varying[]
                   := ''{(''' ||
                      (SELECT STRING_AGG(DISTINCT guid, ''', ''')
                       FROM lc) ||
                      ''')}'';
                r character varying;
             begin
                foreach r in array guids_list 
                loop
                   insert into _x17092018
                      select r, t.*
                      FROM sm.func_by_houseid(r, TRUE, ''db'') as t;
                end loop;
             END$$;'
         ) AS addr(full_address TEXT)
   );

Laurenz Albe · Accepted Answer · 2018-09-18 06:54:42Z

1

The error must come from the initialization of guids_list.

The way it is written, it will come out as something like

{[guid1', 'guid ' containing spaces and quote', 'guid3]}

which is clearly not what you intend. Besides, as I tried to demonstrate, it is open to SQL injection.

You could use something like

'guids_list character varying[] := ' ||
   (SELECT quote_literal(array_agg(DISTINCT guid)) FROM lc) || ';'

answered Sep 18, 2018 at 6:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

SQL Error[42601]: Error: syntax error at or near "0"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related