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 CACpWLjM=d55Mvg9d+4OQ1HcdTjMHC79fqSzr8SROtDP8qQ8eRg@mail.gmail.com
Whole thread Raw
In response to Re: Name/Value array to table row  (Michael Moore <michaeljmoore@gmail.com>)
List pgsql-sql
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
      ) 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:
On Mon, Dec 14, 2015 at 6:20 PM, Michael Moore <michaeljmoore@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.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.




pgsql-sql by date:

Previous
From: Michael Moore
Date:
Subject: Re: Name/Value array to table row
Next
From: Michael Moore
Date:
Subject: cant get what I want from array_to_json (simple)