Re: Name/Value array to table row

So far this is what I got. I think this will do the trick. Thanks for your
help.
INSERT INTO qsn_app.tdatacapture
SELECT *
FROM json_populate_record(null::qsn_app.tdatacapture,
(SELECT '{"datacapturekey":1,
"dataheader2tdataheader":7777777,
"identifier01":4444444444,
"identifiertype2tcode":1251,
"datacapturestatus2tcode":5020,
"createddate":"2013-08-20 14:52:49",
"updateddate":"2013-08-20 14:52:49",
"trunc_createddate":"2013-08-20 14:52:49",
"trunc_mon_createddate":"2013-08-20 14:52:49",
"createdby2tuser":95959,
"updatedby2tuser":95959,
"status2trecordstatus":"A",
"static01":"test"}'::json))

On Tue, Dec 15, 2015 at 10:54 AM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> 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.
>>
>>
>>
>