Re: Use array in a dynamic statement

Hello

you can't to use parameters inside literal. There hasn't any sense (to
8.3, 8.4 will support USING).

you have to use serialisation to string and quoting.

some like

CREATE OR REPLACE FUNCTION foo(int[])
RETURNS SETOF int AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE
'SELECT (' || quote_literal($1::text) ||
'::int[])[i] AS x
FROM generate_series(1, array_upper(' ||
quote_literal($1::text) || '::int[],1)) g(i)'
LOOP
RETURN NEXT r.x;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo(ARRAY[1,2,3]);

CREATE OR REPLACE FUNCTION foo84(int[])
RETURNS SETOF int AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE
'SELECT $1[i] AS x FROM generate_series(1,
array_upper($1,1)) g(i)' USING $1
LOOP
RETURN NEXT r.x;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo84(ARRAY[1,2,3]);

regards
Pavel Stehule

2009/3/5 Sophie Yang <yangsophie(at)yahoo(dot)com>:
>
> 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.
>
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>