PostgreSQL: Select string_agg into variable

Question

I have a string to execute with the string aggregate function within the postgresql function. Here is the following script for that.

Example:

create or replace function f(colvalue int,colnvalue varchar)
returns void as
$$
declare
      sql varchar;
      var varchar;
begin
     sql := 'Select var:= string_agg(................) /* Error occurred here near var:= */
             from tablename where cola ='|| colvalue || ' AND coln ='|| colnvalue;

     raise notice '%'sql;

     execute sql into var;

     raise notice var;

end;
$$
language plpgsql;

Error:

ERROR:  syntax error at or near ":="

Note: I want the result of string_agg into var.

Pavel Stehule · Accepted Answer · 2014-07-14 06:58:33Z

2

Dynamic SQL can contains SQL statement only - but ":=" is a PL/pgSQL statement. Next, it is clean from your example, so it is useless there. Second issue is a SQL injection vulnerability (still this code should not work). Newer use a patter ' || varcharvar || ' for SQL used in dynamic SQL.

CREATE OR REPLACE FUNCTION f(colvalue int,colnvalue varchar)
RETURNS void AS $$
DECLARE
  sql varchar;
  var varchar;
BEGIN
  sql := 'SELECT string_agg(..) FROM tablename WHERE cola=$1 AND coln=$2';
  RAISE NOTICE '%', sql;
  EXECUTE sql INTO var USING colvalue, colnvalue;
  RAISE NOTICE '%', var;
END;
$$ LANGUAGE plpgsql;

answered Jul 14, 2014 at 6:58

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

2 Comments

Sarfaraz Makandar Over a year ago

Thank you so much. But getting <NULL> string in var variable. :(

Sarfaraz Makandar Over a year ago

Yeah! I Solved it. Thank you so much for your valuable assistance.

Collectives™ on Stack Overflow

PostgreSQL: Select string_agg into variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related