I was wondering why USING clause is not supported in pl/pgsql dynamic
statement. Serialization is the option I tried to avoid, but it seems there is
no better approach available.
Just to say a few more about the usage of my function. In dag_tree_1, (rid,
rtid) is the primary key, which identifies a node in a tree structure. The idx
field is a kind of dewy index. for example:
rid rtid idx
1123 1 .0006.0033
3231 1 .0006
786 6 .0007.8853
80923 2 .0007.8853.2382
The function takes in a list of rid and rtid pair (nids), sort them by the
length of the dewy index, which is equivalent to sort the nodes by their tree
depth. That's what I try to achieve. Maybe someone has different idea to
implement the function?
Thanks,
Sophie
- Original Message
From: Pavel Stehule pavel.steh...@gmail.com
To: Sophie Yang yangsop...@yahoo.com
Cc: pgsql-hackers@postgresql.org
Sent: Thursday, March 5, 2009 12:06:24 AM
Subject: Re: [HACKERS] 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
CREATEOR REPLACE FUNCTION foo(int[])
RETURNSSETOF int AS $$
DECLAREr 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 yangsop...@yahoo.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
