Thread: Name/Value array to table row
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);
thanks,
Mike
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.EARRAYname |value-----------animal | 'cat'lbs | 20create 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.
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.g.johnston@gmail.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.EARRAYname |value-----------animal | 'cat'lbs | 20create 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.
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@gmail.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) ISt_column_values XPORTAL_DATACAPTURE_TABLE;--snip---later t_column_values is accessed thusly:FOR indx IN t_column_values.FIRST .. t_column_values.LAST LOOPv_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 wouldsuggest for pcolumn_values_i keeping in mind that ultimately the name/value pairs correspond to Table Column names.Thanks!MikeOn Mon, Dec 14, 2015 at 5:31 PM, David G. Johnston <david.g.johnston@gmail.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.EARRAYname |value-----------animal | 'cat'lbs | 20create 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.