Re: Use array in a dynamic statement

Sophie Yang wrote:
> 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? 

In 8.4, there will be a EXECUTE '...' USING construct that you could 
use: 
http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN.

I don't quite understand what the function is trying to do, but in 
existing releases you could store the input array into a temporary 
table, or rewrite the function to do its magic in a for loop instead of 
a single query.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com