Обсуждение: Use array in a dynamic statement
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.
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
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@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
>
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
anode in a tree structure. The idx field is a kind of dewy index. for example:
rid rtid idx
1123 1 0000.0006.0033
3231 1 0000.0006
786 6 0000.0007.8853
80923 2 0000.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
equivalentto sort the nodes by their tree depth. That's what I try to achieve. Maybe someone has different idea to
implementthe function?
Thanks,
Sophie
----- Original Message ----
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Sophie Yang <yangsophie@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
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@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
>
On Thu, Mar 5, 2009 at 8:32 PM, Sophie Yang <yangsophie@yahoo.com> wrote: > > I was wondering why USING clause is not supported in pl/pgsql dynamic statement. Serialization is the option I tried toavoid, 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 identifiesa node in a tree structure. The idx field is a kind of dewy index. for example: > rid rtid idx > 1123 1 0000.0006.0033 > 3231 1 0000.0006 > 786 6 0000.0007.8853 > 80923 2 0000.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 equivalentto sort the nodes by their tree depth. That's what I try to achieve. Maybe someone has different idea to implementthe function? Well the function is pretty simple. Maybe you could merge the logic encapsulated by the function into the containing query? ...Robert
I don't see how the problem can be solved by merging the function logic into the calling function. The int[][] array is
suppliedby user, and I still have the issue to pass it into a dynamic statement, no matter in which function, unless I
serializeit to string and deserialize back to int[][].
----- Original Message ----
From: Robert Haas <robertmhaas@gmail.com>
To: Sophie Yang <yangsophie@yahoo.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-hackers@postgresql.org
Sent: Thursday, March 5, 2009 6:51:48 PM
Subject: Re: [HACKERS] Use array in a dynamic statement
On Thu, Mar 5, 2009 at 8:32 PM, Sophie Yang <yangsophie@yahoo.com> wrote:
>
> I was wondering why USING clause is not supported in pl/pgsql dynamic statement. Serialization is the option I tried
toavoid, 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
identifiesa node in a tree structure. The idx field is a kind of dewy index. for example:
> rid rtid idx
> 1123 1 0000.0006.0033
> 3231 1 0000.0006
> 786 6 0000.0007.8853
> 80923 2 0000.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
equivalentto sort the nodes by their tree depth. That's what I try to achieve. Maybe someone has different idea to
implementthe function?
Well the function is pretty simple. Maybe you could merge the logic
encapsulated by the function into the containing query?
...Robert