From: | Sophie Yang <yangsophie(at)yahoo(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Use array in a dynamic statement |
Date: | 2009-03-05 07:36:01 |
Message-ID: | 446858.13169.qm@web53712.mail.re2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Xavier Bugaud | 2009-03-05 07:39:33 | Re: Patch for the MUST time zone (Mauritius Summer Time) |
Previous Message | KaiGai Kohei | 2009-03-05 07:32:28 | Re: [BUG] Column-level privileges on inherited tables |