Re: Name/Value array to table row - Mailing list pgsql-sql
| From | Michael Moore |
|---|---|
| Subject | Re: Name/Value array to table row |
| Date | |
| Msg-id | CACpWLjPsEYhVbcZeKxa8zv5fwHf7fAXeAkuD59P5Wh=wT6d4WQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Name/Value array to table row ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Name/Value array to table row
(Michael Moore <michaeljmoore@gmail.com>)
|
| List | pgsql-sql |
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.