Re: Name/Value array to table row

Hi David,
Yes, I was a bit vague on my description of the array. This was deliberate
because it's still undecided. I am doing an Oracle package convert with a
SIGNATURE of :

PROCEDURE dManageRecord
( pdatacapturekey_i IN tdatacapture.datacapturekey%TYPE,
*pcolumn_values_i IN XPORTAL_DATACAPTURE_TABLE*,
pScopes_i IN CLOB DEFAULT NULL,
pUserProfileXML_i IN XMLTYPE DEFAULT NULL
) IS
t_column_values XPORTAL_DATACAPTURE_TABLE;
--snip---

*later **t_column_values is ** accessed thusly:*
FOR indx IN t_column_values.FIRST .. t_column_values.LAST LOOP
v_col_name := UPPER(t_column_values(indx).col_name);
v_col_value := t_column_values(indx).col_value;

*This is how that array is defined:*
CREATE OR REPLACE TYPE QSN_APP."COLUMN_NAME_VALUE_OBJECT"
AS OBJECT (
col_name VARCHAR2(30),
col_value VARCHAR2(4000)
)
/
CREATE OR REPLACE TYPE QSN_APP."XPORTAL_DATACAPTURE_TABLE" as table of
COLUMN_NAME_VALUE_OBJECT
/

So........... let me expand my original question to also ask what datatype
you would
suggest for *pcolumn_values_i keeping in mind that ultimately the
name/value pairs correspond to Table Column names.*

*Thanks!*
*Mike*

On Mon, Dec 14, 2015 at 5:31 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Dec 14, 2015 at 6:20 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
> wrote:
>
>> I have an array that is a name value pair where the name is the name of a
>> column in a table. There are about 300 columns. I want to use all of the
>> name value pairs to construct the table row. I've thought about using jsonb
>> or hstore. I'm sure I could write some ugly code to do this, but what is
>> the BEST way to go about this?
>> I.E
>> ARRAY
>> name |value
>> -----------
>> animal | 'cat'
>> lbs | 20
>>
>> create table animals (animal text, lbs number);
>>
>>
> ​I cannot interpret your pseudo-code shorthand array stuff but I suspect
> the "table row construction" ​would be most simply implemented using
> "json_populate_record(...)" and preceding code can focus on transforming
> your array into the equivalent json. Using the array directly would likely
> require you to construct dynamic SQL in a plpgsql function.
>
> Given my confusion regarding the form of your array it is not possible to
> advise on how such a conversion (to json) function/query would look. There
> are considerable array and json functions that should make doing this step
> reasonably straight-forward. unnest() may enter into the picture.
>
> David J.
>
>
>