Thread: Proposal: associative arrays for plpgsql (concept)
Hello The concept is from Oracle 9i, but with some changes. http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_colls.htm#i35672 Associative arrays are any arrays with index. Will be created DECLARE x varchar[] INDEX BY VARCHAR = '{}'; -- some format, haven't idea then I can use anywhere x[key]; two enhancing FOR cycle: -- iteration over all values FOR i IN VALUES OF x LOOP -- x array or associative array END LOOP; -- iteration over all keys FOR i IN INDICIES OF x LOOP -- x associatice array x[i] END LOOP; new functions: exists(x, key); delete(x, key); index is accessable only from PL/pgSQL. Associative arrays can be spec PostgreSQL type or clasic arrays with hash index. Comments, notes? Regards Pavel Stehule
On Wed, Jun 29, 2005 at 05:59:26PM +0200, Pavel Stehule wrote: > Hello > > The concept is from Oracle 9i, but with some changes. > > http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_colls.htm#i35672 > > Associative arrays are any arrays with index. Will be created > > DECLARE > x varchar[] INDEX BY VARCHAR = '{}'; -- some format, haven't idea > > then I can use anywhere x[key]; > > two enhancing FOR cycle: > > -- iteration over all values > FOR i IN VALUES OF x LOOP -- x array or associative array > END LOOP; > > -- iteration over all keys > FOR i IN INDICIES OF x LOOP -- x associatice array > x[i] > END LOOP; > > new functions: > > exists(x, key); > delete(x, key); > > index is accessable only from PL/pgSQL. Associative arrays can be spec > PostgreSQL type or clasic arrays with hash index. > > Comments, notes? > > Regards > Pavel Stehule I'm all in favor of having associative arrays as a 1st-class data type in PostgreSQL. How much harder would it be to make these generally available vs. tied to one particular language? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
Pavel, > The concept is from Oracle 9i, but with some changes. > > http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_coll >s.htm#i35672 How does this match the SQL2003 spec? -- Josh Berkus Aglio Database Solutions San Francisco
David Fetter <david@fetter.org> writes: > I'm all in favor of having associative arrays as a 1st-class data type > in PostgreSQL. How much harder would it be to make these generally > available vs. tied to one particular language? We already have them--they're called "tables with primary keys". :) What's the use-case for these things? Just imitating Oracle? -Doug
On Wed, 29 Jun 2005, Josh Berkus wrote: > Pavel, > > > The concept is from Oracle 9i, but with some changes. > > > > http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_coll > >s.htm#i35672 > > How does this match the SQL2003 spec? > > I don't know. What I can read about it, it's only PL/SQL feature and maybe reason for PL/pgSQL. I like and need a) hash arraysb) iteration over all items of array All I can use well in my codes. Pavel
On Wed, 29 Jun 2005, Douglas McNaught wrote:
> David Fetter <david@fetter.org> writes:
>
> > I'm all in favor of having associative arrays as a 1st-class data type
> > in PostgreSQL. How much harder would it be to make these generally
> > available vs. tied to one particular language?
>
> We already have them--they're called "tables with primary keys". :)
>
> What's the use-case for these things? Just imitating Oracle?
>
> -Doug
>
no
for example
DECLARE _d varchar[] INDEX BY VARCHAR = {'cmd1' => '723:t:f:1', 'cmd2'=>..
BEGIN FOR r IN SELECT * FROM data LOOP check_params(_r, _d[_r.cmd]) END LOOP;
or without assoc. arrays
DECLARE _d varchar;
BEGIN FOR r IN SELECT * FROM data LOOP SELECT INTO par _d WHERE cmd = _r.cmd; check_params(_r, _d) END LOOP;
I can't to speak about speed without tests but I can expect so hash array
can be much faster. This sample is easy, but I can have procedure witch
operate over big arrays of numbers(prices) and I need save somewhere this
arrays if I don't wont to read them again and again. And if I have in
data identification by key, I everytime have to find key, and translate it
into number
Regards
Pavel Stehule
Pavel Stehule wrote: >On Wed, 29 Jun 2005, Josh Berkus wrote: > > > >>Pavel, >> >> >> >>> The concept is from Oracle 9i, but with some changes. >>> >>>http://www.oracle-10g.de/oracle_10g_documentation/appdev.101/b10807/05_coll >>>s.htm#i35672 >>> >>> >>How does this match the SQL2003 spec? >> >> >> >> > >I don't know. What I can read about it, it's only PL/SQL feature and maybe >reason for PL/pgSQL. > >I like and need > > a) hash arrays > b) iteration over all items of array > >All I can use well in my codes. > > > > Well, plperl and pltcl will buy you these (not to mention plruby and even pljavascript when I get around to creating it) That's not to say that we should not build them into plpgsql, but to suggest that there might be reasonable alternatives. cheers andrew
On Wed, Jun 29, 2005 at 01:20:17PM -0400, Douglas McNaught wrote: > David Fetter <david@fetter.org> writes: > > > I'm all in favor of having associative arrays as a 1st-class data > > type in PostgreSQL. How much harder would it be to make these > > generally available vs. tied to one particular language? > > We already have them--they're called "tables with primary keys". :) > > What's the use-case for these things? Just imitating Oracle? It would make named function parameters *very* easy to do. :) SELECT * FROM foo_func( a => 2, b => 5, c => current_timestamp::timestamp with time zone ); would be equivalent to SELECT * FROM foo_func( c => current_timestamp::timestamp with time zone, a => 2, b => 5 ); and both would Do The Right Thing. It also opens the door to default parameters for those who want them. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!