Use array in a dynamic statement
От | Sophie Yang |
---|---|
Тема | Use array in a dynamic statement |
Дата | |
Msg-id | 446858.13169.qm@web53712.mail.re2.yahoo.com обсуждение исходный текст |
Ответы |
Re: Use array in a dynamic statement
Re: Use array in a dynamic statement |
Список | pgsql-hackers |
Hi, I am trying to implement a PL/PgSQL function as following: CREATE OR REPLACE FUNCTION sort_by_d_idx (nids INT[][], tbl_name VARCHAR) RETURNS varchar[] AS $$ DECLARE result varchar[]; BEGIN EXECUTE 'SELECT ARRAY(SELECT t.idx FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i), ' ||tbl_name||'t WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER BY length(t.idx))' INTO result; RETURN result; END; $$ LANGUAGE plpgsql; I got an error "ERROR: there is no parameter $1" when I test the function with: select sort_by_d_idx('{{8148, 1}, {8149, 1}, {300, 2}, {8163, 1}, {8170, 1}}'::int[][], 'd_tree_1'); The error is understandable, but my question is how to supply the int[][] array into the dynamic SQL? To help understand the dynamic statement, the structure of d_tree_1 is (rid, rtid, idx). The PK is (rid, rtid) pair. If the tbl_name is fixed, the following function works well: CREATE OR REPLACE FUNCTION sort_by_d_idx2 (nids INT[][]) RETURNS varchar[] LANGUAGE SQL AS $$ SELECT ARRAY( SELECT t.idx FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i), d_tree_1 t WHERE$1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER BY length(t.idx) ); $$; Unfortunately, the tbl_name is determined at query time. Please help.
В списке pgsql-hackers по дате отправления: