Thread: how to key/value iterate in stored function
I want to create a variable that is an array or list of key/value pairs. The key would be a table name and the value would be a sql statement or a value or list of values for which I could extract to create a sql statement and execute. I have tried experimenting with various arrays, but there is no clear example or documentation. The key and the value would be text. I want to iterate over the entire array (or list), so would need an example on that or some pointers there, if there is help. J.V.
On Wed, Oct 12, 2011 at 6:46 AM, J.V. <jvsrvcs@gmail.com> wrote: > I want to create a variable that is an array or list of key/value pairs. > > The key would be a table name and the value would be a sql statement or a > value or list of values for which I could extract to create a sql statement > and execute. > > I have tried experimenting with various arrays, but there is no clear > example or documentation. The key and the value would be text. > > I want to iterate over the entire array (or list), so would need an example > on that or some pointers there, if there is help. > Which version of PostgreSQl are you running? It's a little easier on 8.4 and higher (unnest comes in handy here), but prior to 1.4 you can loop from array_lower to array_upper..... Best Wishes, Chris Travers
On Wed, Oct 12, 2011 at 8:46 AM, J.V. <jvsrvcs@gmail.com> wrote:
> I want to create a variable that is an array or list of key/value pairs.
>
> The key would be a table name and the value would be a sql statement or a
> value or list of values for which I could extract to create a sql statement
> and execute.
>
> I have tried experimenting with various arrays, but there is no clear
> example or documentation. The key and the value would be text.
>
> I want to iterate over the entire array (or list), so would need an example
> on that or some pointers there, if there is help.
use thee the hstore!
postgres=# select * from each('a=>1,b=>2');
key | value
-----+-------
a | 1
b | 2
(2 rows)
(if you do stick with arrays, use unnest() -- it can be built for
older versions if you don't have it).
merlin
I tried hstore with no luck.
How do I initialize the array?
How do I loop through each key/value pair in a stored function to
retrieve the key and value for each item in the list?
I need a list of key value pairs. Any options there?
J.V.
On 10/12/2011 8:51 AM, Merlin Moncure wrote:
> On Wed, Oct 12, 2011 at 8:46 AM, J.V.<jvsrvcs@gmail.com> wrote:
>> I want to create a variable that is an array or list of key/value pairs.
>>
>> The key would be a table name and the value would be a sql statement or a
>> value or list of values for which I could extract to create a sql statement
>> and execute.
>>
>> I have tried experimenting with various arrays, but there is no clear
>> example or documentation. The key and the value would be text.
>>
>> I want to iterate over the entire array (or list), so would need an example
>> on that or some pointers there, if there is help.
> use thee the hstore!
>
> postgres=# select * from each('a=>1,b=>2');
> key | value
> -----+-------
> a | 1
> b | 2
> (2 rows)
>
> (if you do stick with arrays, use unnest() -- it can be built for
> older versions if you don't have it).
>
> merlin
>
On Oct 12, 2011, at 14:43, "J.V." <jvsrvcs@gmail.com> wrote:
> I tried hstore with no luck.
>
> How do I initialize the array?
>
> How do I loop through each key/value pair in a stored function to retrieve the key and value for each item in the
list?
>
> I need a list of key value pairs. Any options there?
>
> J.V.
>
> On 10/12/2011 8:51 AM, Merlin Moncure wrote:
>> On Wed, Oct 12, 2011 at 8:46 AM, J.V.<jvsrvcs@gmail.com> wrote:
>>> I want to create a variable that is an array or list of key/value pairs.
>>>
>>> The key would be a table name and the value would be a sql statement or a
>>> value or list of values for which I could extract to create a sql statement
>>> and execute.
>>>
>>> I have tried experimenting with various arrays, but there is no clear
>>> example or documentation. The key and the value would be text.
>>>
>>> I want to iterate over the entire array (or list), so would need an example
>>> on that or some pointers there, if there is help.
>> use thee the hstore!
>>
>> postgres=# select * from each('a=>1,b=>2');
>> key | value
>> -----+-------
>> a | 1
>> b | 2
>> (2 rows)
>>
>> (if you do stick with arrays, use unnest() -- it can be built for
>> older versions if you don't have it).
>>
>> merlin
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Create a custom type with two text components and use that in an array.
David J.
I tried that, but it did not work.
what i am looking for is a means to iterate over an array or list fetching both the key and the value.
if you have such an example, it would be appreciated.
>
>Create a custom type with two text components and use that in an array.
>
>David J.
On 10/12/2011 1:11 PM, David Johnston wrote:
>
> On Oct 12, 2011, at 14:43, "J.V."<jvsrvcs@gmail.com> wrote:
>
>> I tried hstore with no luck.
>>
>> How do I initialize the array?
>>
>> How do I loop through each key/value pair in a stored function to retrieve the key and value for each item in the
list?
>>
>> I need a list of key value pairs. Any options there?
>>
>> J.V.
>>
>> On 10/12/2011 8:51 AM, Merlin Moncure wrote:
>>> On Wed, Oct 12, 2011 at 8:46 AM, J.V.<jvsrvcs@gmail.com> wrote:
>>>> I want to create a variable that is an array or list of key/value pairs.
>>>>
>>>> The key would be a table name and the value would be a sql statement or a
>>>> value or list of values for which I could extract to create a sql statement
>>>> and execute.
>>>>
>>>> I have tried experimenting with various arrays, but there is no clear
>>>> example or documentation. The key and the value would be text.
>>>>
>>>> I want to iterate over the entire array (or list), so would need an example
>>>> on that or some pointers there, if there is help.
>>> use thee the hstore!
>>>
>>> postgres=# select * from each('a=>1,b=>2');
>>> key | value
>>> -----+-------
>>> a | 1
>>> b | 2
>>> (2 rows)
>>>
>>> (if you do stick with arrays, use unnest() -- it can be built for
>>> older versions if you don't have it).
>>>
>>> merlin
>>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
> Create a custom type with two text components and use that in an array.
>
> David J.
On Wed, Oct 12, 2011 at 1:43 PM, J.V. <jvsrvcs@gmail.com> wrote: > I tried hstore with no luck. > > How do I initialize the array? > > How do I loop through each key/value pair in a stored function to retrieve > the key and value for each item in the list? > > I need a list of key value pairs. Any options there? it's all in the docs: postgres=# select * from each(hstore(ARRAY['a','b'], ARRAY['1','2'])); key | value -----+------- a | 1 b | 2 (2 rows) dont see what's so hard 'bout that. 9.0+ hstore is superior to the composite type method in every way I can think of unless you are storing explictly non text value in the type and you are not transferring the compacted list to the client. hstore is much more flexible in terms of getting data in/out, searching, etc. as a bonus you have gist indexing if you need it, etc etc. merlin