Re: 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(at)fetter(dot)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(at)fetter(dot)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 arrays
b) 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(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!